Timezone Solution for mySQL

I am new to SQL languages and I recently spent about two hours trying to bring some order to timestamps in my PTM project. It was obvious that I should store universal UTC/GMT date/times in the database. It was not so clear how to convert these back to local time. Examples I found on the web were all commands typed at the mysql prompt, not database queries. After quite a bit of poking around I finally stumbled on the AS Keyword in mySQL. Seems to work well so I decided to share the solution here for the next newbie to learn from!

SELECT id, date, CONVERT_TZ(date,'+00:00','-04:00') AS corrected_date
FROM `some_table`
WHERE some_criteria = 'something'

This yields output that is nicely localized and completely abstracted from the actual data. Notice that the line for id=27 handles midnight properly.

Output
id date corrected_date
1 2012-06-24 11:46:15 2012-06-24 07:46:15
21 2012-04-23 18:22:21 2012-04-23 14:22:21
28 2011-06-24 11:12:44 2011-06-24 07:12:44
27 2012-04-23 01:53:51 2012-04-22 21:53:51
29 0000-00-00 00:00:00 NULL
30 2012-06-24 00:00:00 2012-06-23 20:00:00
10 2012-06-12 17:12:37 2012-06-12 13:12:37
31 2012-05-06 13:14:35 2012-05-06 09:14:35

A Video Script That Degrades Gracefully

A few years ago I was looking for a clean way to embed mp4 video into my blog. Prior to HTML5 the best approach was a Flash-based video player. Of course there is no Flash on iPhones and similar devices. Plus many people avoid Flash because of bugs and security concerns. So I modified this script so it defaults to JWPlayer when Flash is present

…but degrades gracefully into a screen shot linked to the mp4 when Flash is absent

…and displays the video in a separate window or helper app…

[see the script in action]

The code appears below. You must replace all the “V_” placeholders with specific information about the video. The path to the player software should be adjusted to match your configuration. The <div> ID should be unique on your site to prevent collisions. This setup assumes there is a jpg key frame file with the same V_NAME as the mp4. Make sure you adjust the size to suit your needs. I find that 320×240 works well embedded in a content area 500-600 pixels wide. The actual mp4 is 640×480, which looks pretty good even when expanded to full screen.

How does it work? Basically the script attempts to load the player and write the display code into the <div> container. If it fails, the default <div> contents are displayed instead (key frame and link). <smile>

<script src="/jw/swfobject.js" type="text/javascript"></script>

<div id="V_DIV" style="text-align: center;">
<a href="/wp-content/videos/V_NAME.mp4" target="_blank">
<img src="/wp-content/videos/V_NAME.jpg" alt="" />
</a><br />V_SIZE Mb Video
</div>

<script type="text/javascript"> var so = new SWFObject('/jw/player.swf','mpl','320','240','9');
so.addParam('allowfullscreen','true');
so.addParam('allowscriptaccess','never');
so.addParam('wmode','opaque');
so.addVariable('controlbar','over');
so.addVariable('autostart','false');
so.addVariable('file','/wp-content/videos/V_NAME.mp4');
so.addVariable('image','/wp-content/videos/V_NAME.jpg');
so.addVariable('title','V_TITLE');
so.addVariable('author','V_AUTHOR');
so.write('V_DIV');
</script>

[Note that if you cut and paste the code above you should remove most of the line breaks I added to make it display properly.]