MySql TimeStamp and JavaScript Time

Question

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 ?

Answer 1

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 ?

Answer 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.