What Time Is It?

Wave 953

Tuesday

The 22nd of December, 2009 at 03:25 PM

So if you are one of my estute web users, you may have noticed that Blueshallow's internal clock is a ... little slow. In fact, precisely 1 hour slow. I however like to think of it in a different light: My site is soooo good that your requests are processed an hour before you even make them. Clairvoyant? I think so... A simple experiment yielding my comment system will illustrate the "error" that I have not had the time to fix. No irony intended. For example: If you post a comment, it will appear as if you did so one hour before. This is the sole purpose of this post, to explain this timeless error and how it should be addressed.

For starters, I believe everyone is familiar with timezones. If you are not, here is a map:

US Timezone Map

Now, I assume the majority of my viewers are in the Central Standard Time area. However, no matter where you are my site is always in the same place. Utah, that's right... Utah. My website is sitting on a computer in Utah which as you can see from the map is in Mountain Standard Time (if you passed Geography you should've figure this one out ;) ).

Now there are a variety of ways to fix this issue, here is what I did. I understand many of you may not care about this "trivial" issue but us web designers get frustrated when everything doesn't work exactly how we want it to. Therefore, here is the Blueshallow Tutorial for changing your internal clock (for websites of course).

Prelude: Before the "fix," Blueshallow was using a UNIX TIMESTAMP. This value is the number of seconds since the January 1st, 1970. More historical information can be found at wikipedia.org. Whatever the case, this makes for a messy database and poor use of the database's internal date functions. More explanation to follow.

Quick Key Points:

The server is in Utah, so the time should stay Utah-an. Therefore, the GLOBAL Server Time stays the same.
All time conversions from Central Standard to Mountain Standard are handled at the time of upload/modification. This is performed by ->
SQL Session is set to current timezone, which is Central Standard Time. This basically translates into a imaginary millisecond plane flight for my website server from Utah to Midwestern US.
Once these timezone variables are set, all subsequent queries (website requests) will be performed in the "appropriate" timezone.

In the database itself, I needed to find a way to transition all current Unix Timestamps to Datetime Format (YYYY-MM-DD HH:MM:SS). This is the necessary format in mysql for datetime. This format allows mysql to perform powerful queries on any of the applicable tables. The timestamp entries are also easier to read to the web designer editing them. Lastly, with datetime format there are no boundaries on either end of the scale (Unix Timestamps have limits). To transition the Unix Timestamps to Datetime Format I performed the following (all table/column names changed to protect the innocent database):

Created a column called `new_time` in datetime format.
Performed query
UPDATE `my_journal` SET `new_time` = FROM_UNIXTIME( `old_time` ) ORDER BY `id` DESC
Now I have all the Unix Timestamps in Datetime Format in my new column `new_time.`

That was easy, the hard part is the code. And by hard, I mean labor intensive.

Set Mysql Session timezone for CST just after mysql connection achieved:
SET time_zone = '-6:00';
Several spots on my website have several different time formats. Here is the gist of it. Using a mysql query you can create a "imaginary" column, or alias if you will. In this imaginary column I recreated all the time formats in the desired format for that particular query. Here is an example timestamp, query and its subsequent output date
- Unix Timestamp = 1254694980
- Datetime Format Created in `new_time` = 2009-10-04 22:23:00
- SELECT *, DATE_FORMAT(`new_time`, '%M %D, %Y at %l:%i %p') as `cute_date` FROM `my_journal` WHERE `id`='".$page_id."'
- Date Output On Website = October 4th, 2009 at 10:23 PM
In other words, all of the PHP date() functions I've used are going to be no longer needed as the work is already performed on the front end of the query. This optimizes processing time, loading time for viewers, and again makes things much easier to read/interpret in the database.
Now just imagine, every little spot you see a date on these page, I had to go through and perform all of the above operations to ensure the time was "correct" in terms of my time zone. Not only that, but it gave me the much needed opportunity to clean up my code and condense it in particular areas.

This concludes a major "redesign" of the backbone of Blueshallow. If you notice any fulminant errors or questions, please contact me at john@blueshallow.com. Now I just have to configure the adminstrative panel! All this for an extra hour :)

My next task, bring this knowledge to my work website and implement all necessary changes. A much larger task than I care to partake in :(