I have a time stamp in a MySQL database in GMT time [2013-07-19 10:12:56]. I know it should be in the DB as a DateTime and UTC but unfortunately this is the way it is.
I need to extract the timestamp and pass into JavaScript [HighCharts].
$time = strtotime('2013-07-19 10:12:56');
echo("Converting to UNIX Time: ");echo $time;
echo("Converting to JS Time: ");echo ($time*1000);
As JavaScript takes time in milliseconds hence the multiply buy 1000
Output: Converting to UNIX Time: 1374253976
Output: Converting to JS :Time: 1374253976000
The question I have is why does it e up in HighCharts as 17.12
When I put 1374253976000 into I get:
GMT: Fri, 19 Jul 2013 17:12:56 GMT which is incorrect. The time should e up as displayed in the database as 10:12:56.
Any ideas why it would be 7h out ?
I have a time stamp in a MySQL database in GMT time [2013-07-19 10:12:56]. I know it should be in the DB as a DateTime and UTC but unfortunately this is the way it is.
I need to extract the timestamp and pass into JavaScript [HighCharts].
$time = strtotime('2013-07-19 10:12:56');
echo("Converting to UNIX Time: ");echo $time;
echo("Converting to JS Time: ");echo ($time*1000);
As JavaScript takes time in milliseconds hence the multiply buy 1000
Output: Converting to UNIX Time: 1374253976
Output: Converting to JS :Time: 1374253976000
The question I have is why does it e up in HighCharts as 17.12
When I put 1374253976000 into http://www.epochconverter. I get:
GMT: Fri, 19 Jul 2013 17:12:56 GMT which is incorrect. The time should e up as displayed in the database as 10:12:56.
Any ideas why it would be 7h out ?
Share Improve this question edited Jun 20, 2020 at 9:12 CommunityBot 11 silver badge asked Jul 22, 2013 at 8:03 DevilCodeDevilCode 1,1005 gold badges36 silver badges63 bronze badges 13- Have you tried to disable UTC time (api.highcharts./highstock#global.useUTC) – Sebastian Bochan Commented Jul 22, 2013 at 10:48
- Yes but its still 7h in the future. var highchartsOptions = Highcharts.setOptions(Highcharts.theme,{ global: { useUTC: false }}); – DevilCode Commented Jul 22, 2013 at 12:00
- Its strange becase when i run i.e new Date(1374253976000) is also correct data (as yours). – Sebastian Bochan Commented Jul 22, 2013 at 12:01
- You are getting the same problem ? – DevilCode Commented Jul 22, 2013 at 12:04
- I receive correct data, so cannot replicate this problem. – Sebastian Bochan Commented Jul 22, 2013 at 12:13
4 Answers
Reset to default 11 +400Epochtimeconverter says that Fri, 19 Jul 2013 10:12:56 GMT has following epoch times
Epoch timestamp: 1374228776
Timestamp in milliseconds: 1374228776000
Human time (GMT): Fri, 19 Jul 2013 10:12:56 GMT
Human time (your time zone): Friday, July 19, 2013 3:42:56 PM
Note, it's NOT 1374253976 as you are getting. So that is the problem, the conversion from epochtime to highcharts, etc. is correct. It's your epochtime that is ing to be wrong
PHP will treat the string to be in server's timezone, unless mentioned otherwise.
Give this a try, specify the timezone explicitly as GMT as follows
$time = strtotime('2013-07-19 10:12:56' . ' GMT');
echo("Converting to UNIX Time: ");echo $time;
echo("Converting to JS Time: ");echo ($time*1000);
The 7h difference that you were observing may be because your server is located in a the timezone -7 ?
Problem #1
The primary problem is in your conversion step using strtotime. PHP tries to guess the relationship of the time string to the time zone (probably using your time zone), but in your situation it needs to know more to guess properly. You can do that by explicitly telling PHP that the time string is represented as GMT (Greenwich Mean Time).
Since your database only has the form of YYYY-MM-DD HH:MM:SS, you can concatenate the necessary GMT identifier onto it during your strtotime invocation.
$time = strtotime('2013-07-19 10:12:56' . ' GMT');
For example:
<pre>
<?php
$time = strtotime('2013-07-19 10:12:56' . ' GMT');
echo("Converting to UNIX Time: $time\n");
echo("Converting to JS Time: ");
echo ($time*1000);
?>
</pre>
Converting to UNIX Time: 1374228776
Converting to JS Time: 1374228776000
Pasting the resulting UNIX Time into Epoch Converter results correctly in GMT: Fri, 19 Jul 2013 10:12:56 GMT
Problem #2
Changing your PHP code as above is necessary, but since it is not fully working for you, I can see that you have one more problem going on. (Sebastian tried to help you with this.)
What I would add to Sebastian's ments is that global settings need to be configured BEFORE you create any charts or chart objects in your JavaScript. Otherwise they will have no effect on the output of those charts. So you need to make sure that the following configuration is applied before any chart objects exist in your code.
Highcharts.setOptions({
global: {
useUTC: false
}
});
(If you don't use this configuration, your charts will still work fine, but dates and times will show relative to Greenwich Mean Time instead of the local time zone for each user viewing the charts.)
Discussion (placed afterwards for those who hate discussion don't have to read it):
I verified that if you are following the above suggested modification to your PHP code, then you now have the correct UTC/GMT numeric epoch time passed to JavaScript. You can confirm this 100% for sure with the following code:
Date.UTC(2013, 6, 19, 10, 12, 56)
1374228776000
new Date(1374228776000).toUTCString()
"Mon, 19 Aug 2013 10:12:56 GMT"
The second problem you are apparently facing is two-fold: making Highcharts properly interpret this as a UTC/GMT value rather than a value from some other (such as local) timezone, AND telling Highcharts to properly output the timezone in your desired format.
Making Highcharts properly interpret your time numbers: By default Highcharts assumes numeric time data like these are in UTC [[1331028000000, 5], [1331031600000, 6], [1331035200000, 4]]. Unless there is something strange lurking in your JavaScript and altering how the time data is processed, you should be good in that regard.
Making Highcharts display the correct time in the human-readable dates on your charts: this is where you should play close attention to the API here: http://api.highcharts./highcharts#global.useUTC
By the way, Sebastian Bochan is a member of the Highcharts support team, so you can certainly trust his ments.
You have a timezone issue. At GMT the time-stamp 2013-07-19 10:12:56 translates to 1374228776 seconds after epoch: http://jsfiddle/hsvtE/
Your PHP script however returns the incorrect unix time:
Output: Converting to UNIX Time: 1374253976
As you can see the difference between the two timestamps is:
1374253976 (incorrect)
-1374228776 (correct)
-----------
25200 seconds
The time your PHP script returns is 25200 seconds (or seven hours) ahead of GMT time. The reason PHP returns the wrong time is because of the way the strtotime function works.
If no timezone is specified in the time-stamp then strtotime assumes that the time-stamp belongs to the default timezone.
There are two methods to solve this problem:
Method 1: Set the default timezone to GMT
You can get and set the default timezone in PHP using the functions date_default_timezone_get and date_default_timezone_set. Using these two functions you could set the correct timezone for your timestamp as follows:
function get_time($timestamp, $timezone = 'GMT') {
$default_timezone = date_default_timezone_get(); // get current timezone
date_default_timezone_set($timezone); // set correct timezone
$time = strtotime($timestamp); // get correct time
date_default_timezone_set($default_timezone); // reset original timezone
return $time; // return correct time
}
Now you may safely get the correct time at GMT by simply calling get_time instead of strtotime:
$time = get_time('2013-07-19 10:12:56');
I remend you use this method because if the time-stamp already specifies a timezone then strtotime simply ignores the default timezone and uses the specified timezone instead.
Method 2: Specify the timezone in the time-stamp itself
The second method is much shorter:
function get_time($timestamp, $timezone = 'GMT') {
return strtotime($timestamp . ' ' . $timezone);
}
$time = get_time('2013-07-19 10:12:56');
However I would remend that you use the first method instead because it's safer. For example if you provided the timestamp 2013-07-19 10:12:56 EST then the above function would call strtotime with a string with two timezones - 2013-07-19 10:12:56 EST GMT.
Conclusion
This simple change should solve your problems. If you want the time in milliseconds then simply multiply the time by 1000:
$time = 1000 * get_time('2013-07-19 10:12:56');
I'm assuming that your client side code is correct. Hence if you still have problems then you should take a look at your JavaScript.
In fact it is not a JS problem. The trick resides in PHP and how "strtotime" does its conversion with regard to default timezone.
In your example, the timestamp is calculated as if you were in "America/Los_Angeles" timezone, so you you get 1374253976 seconds instead of 1374228776 with timezone "GMT". Since you're converting timestamp relatively to GMT timezone on epochconverter, a "mismatch" appears.
The data you store in MySQL is in GMT, the timezone of your PHP installation is "America/Los_Angeles". When you "strtotime", you interprete the data into MYSQL as not being GMT but America/Los_Angeles. That's why there are 7 hours between the two.
Here is a little script to better explain this :
date_default_timezone_set('GMT');
echo strtotime('2013-07-19 10:12:56', time() - 10) . "\n";
date_default_timezone_set('America/Los_Angeles');
echo strtotime('2013-07-19 10:12:56', time() - 10) . "\n";
will display :
1374228776 // from epochconverter : Fri, 19 Jul 2013 10:12:56 GMT
1374253976 // from epochconverter : Fri, 19 Jul 2013 17:12:56 GMT
Notice how the sequence "287" is replaced by the sequence "539".
In PHP, almost every function manipulating time will do so based on the currently defined timezone.
Look at this for more informations :
PHP Default TimeZone