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
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.
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>
<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
[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.]