5

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 come up in HighCharts as 17.12

When I put 1374253976000 into http://www.epochconverter.com I get:
GMT: Fri, 19 Jul 2013 17:12:56 GMT which is incorrect. The time should come up as displayed in the database as 10:12:56.

Any ideas why it would be 7h out ?

13
  • Have you tried to disable UTC time (api.highcharts.com/highstock#global.useUTC) Commented Jul 22, 2013 at 10:48
  • Yes but its still 7h in the future. var highchartsOptions = Highcharts.setOptions(Highcharts.theme,{ global: { useUTC: false }}); Commented Jul 22, 2013 at 12:00
  • Its strange becase when i run i.e new Date(1374253976000) is also correct data (as yours). Commented Jul 22, 2013 at 12:01
  • You are getting the same problem ? Commented Jul 22, 2013 at 12:04
  • I receive correct data, so cannot replicate this problem. Commented Jul 22, 2013 at 12:13

4 Answers 4

11
+400

Epochtimeconverter 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 coming 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 ?

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your help, the server was indeed in a -7 timezone.
2

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 comments 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.com/highcharts#global.useUTC

By the way, Sebastian Bochan is a member of the Highcharts support team, so you can certainly trust his comments.

2 Comments

Sorry, I literally did not read the rest of Jugal's answer (stopped at about the word "So that is the problem" because I hadn't seen any solution yet) before clicking answer. I would have seen that he suggested the exact same thing if I had finished reading all of his answer. Oops! I'm leaving my answer up because I think it's a clearer explanation (and I tested it instead of saying try it), but Jugal has the right to claim the bounty.
@DevilCode you are very welcome! Let us know if you have any issues remaining and we will be glad to help you resolve them.
0

You have a timezone issue. At GMT the time-stamp 2013-07-19 10:12:56 translates to 1374228776 seconds after epoch: http://jsfiddle.net/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 recommend 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 recommend 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.

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.