Awesometoast.com

The multipurpose website of Aaron W. Herd

Localize an SQL date using Javascript

So you have a date from your database and it looks like this: 2014-01-31 01:02:03. It so happens that your server is on UTC time. You want to:

  1. Format the date into something prettier, and
  2. Offset the date so it’s adjusted for a user’s local time zone.

Ok, great. You only have to SUFFER THE PAIN OF A THOUSAND MIGRAINES AND oh wait it’s actually sort of easy. But man, this took a lot of pain and searching to find. The key is moment.js, a glorious gift to developer-kind.

So the way I’m doing this is to create an HTML5 <time> element, but it can certainly be done other ways. First, after running a DB query, I have PHP produce this:

<time datetime="2014-01-03 12:57:03">
    2014-01-03 12:57:03
</time>

Not very pleasant to look at. So then we do a little moment.js…

var date = $(time).attr('datetime');
var newformat = 'D MMM YYYY [at] h:mm A';
var nice = moment(date, 'YYYY-MM-DD HH:mm:ss').format(new_format);
$('time').html(nice);

There’s some jQuery in there, too, but it isn’t necessary.

What we just did, is we read the date from our <time> element’s datetime attribute, then told moment what format it was in, so it could then spit it back out how we want it to, using .format(), as the human-readable part of the <time> element.

The result looks like this: 3 Jan 2014 at 12:57 PM. Yay. It’s nicer. But that’s still UTC time, and no timezone data was saved in our DB. So we have to add it.

<time datetime="2014-01-03 12:57:03 +0000">
    2014-01-03 12:57:03 UTC
</time>

Note that the stuff added here didn’t come from the DB, I added them manually. The “+0000″, or an offset of 0 hours for UTC time, is what we’re giving to moment.js. The “UTC” is just there for humans to read, just in case something goes wrong or Javascript is disabled. With those added, we inform moment.js that there’s a timezone offset in there:

var nice = moment(date,'YYYY-MM-DD HH:mm:ss ZZ').format(newformat);

See the “ZZ” we added in the middle? Now moment.js knows we have a timezone and what it is. And just like that, it’s been adjusted: 2 Jan 2014 at 5:57 PM (adjusted for America/Denver, -7:00).

It was so easy, that after all the suffering gone through before this point, I’m almost mad. But not really. But just a little bit.

Posted under: General Stuff

1 comment ↓

#1 by Chamomile on 05.03.14 at 2:31 pm

Slam dunkin like Shaquille O’Neal, if he wrote invifmatore articles.

Leave a Comment