MySQL Timestamp to JavaScript Date not converting correctly

Question

so I have a timestamp in mysql that is:

SELECT `tsdate`, UNIX_TIMESTAMP( `tsdate`) FROM t1
2010-11-07 21:50:05, 1289191805

If I try and use the following, it displays the wrong time/date

var newDate = new Date();
newDate.setTime(1289191805000);
dateString = newDate.toUTCString();
alert(dateString);

Mon, 08 Nov 2010 04:50:05 GMT

How can I get JavaScript to show the correct date?

I'm currently using the tool highcharts to grapth, and it is showing the wrong date/time as well (it's written in JavaScript). I don't want to change the code of highcharts, but I will if needed.

Thanks, Josh

What time zone is the timestamp supposed to be in? Is it UTC too? — Pekka
– Pekka, Commented Jan 13, 2011 at 14:58
If you live in a place that is in the GMT-7 timezone, using .toString() will produce the correct time! — Thai
– Thai, Commented Jan 13, 2011 at 15:01

Seldaek · Accepted Answer · 2011-01-13 15:02:49Z

2

It looks like you have a timezone problem. As you see the javascript one is GMT, while I suspect yours is some western US time?

Try the following in your MySQL query:

SELECT UNIX_TIMESTAMP( CONVERT_TZ( tsdate, '-07:00', 'GMT') ) FROM t1

-07:00 could be replaced by whatever timezone identifier you're in.

An alternative solution could be to do newDate.setTime(mysqlTimestamp + 7*3600000) in JavaScript to only adjust it there.

answered Jan 13, 2011 at 15:02

Seldaek

42.3k10 gold badges100 silver badges78 bronze badges

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

1 Comment

Josh Over a year ago

But I would prefer not to hard code the timezone. What if someone else views data from -8? or -5?

Anthony Grist · Accepted Answer · 2011-01-13 15:27:07Z

0

As previously mentioned, using toString will return it in local time, though it will also have additional Timezone information. A hack to display it in local time, without the additional Timezone information, is to use getTimezoneOffset() (returns a value in minutes) multiplied by sixty (to get it in seconds) multiplied by 1000 (to get it in milliseconds)

var newDate = new Date();
newDate.setTime(1289191805000 - (newDate.getTimezoneOffset() * 60 * 1000));
dateString = newDate.toUTCString();
alert(dateString);

answered Jan 13, 2011 at 15:27

Anthony Grist

38.4k8 gold badges64 silver badges76 bronze badges

Comments

Josh · Accepted Answer · 2011-01-13 15:29:51Z

0

I found the solution at http://highslide.com/forum/viewtopic.php?f=9&t=8613

Highcharts.setOptions({
   global: {
      useUTC: false
   }
});

I also wanted to thank Anthony Grist and Seldaek for some helpful code!

var newDate = new Date();
newDate.setTime(1289191805000 - (newDate.getTimezoneOffset() * 60 * 1000));
dateString = newDate.toUTCString();
alert(dateString);

answered Jan 13, 2011 at 15:29

Josh

8191 gold badge14 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL Timestamp to JavaScript Date not converting correctly

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related