Pardon the Coming Dust

Awhile back, I made the decision to retain the UNIX time stamp system of dating within SAFARI, which I had started using when I first created SAFARI to be used without a database. The UNIX time stamp (seconds from the UNIX epoch of January 1, 1970) is easy to process in Perl, which makes it attractive to work with.

Big problem: this means more of the date processing must be done after a given piece of information is fetched from the db. If I was using the MySQL date-time field format (2005-03-13 16:12:30), I could do some processing within the SQL queries and therefore streamline things. Therefore, I'm going to undertake switching SAFARI over to this, right now. Unfortunately, SAFARI's sandbox that I built shares the database with the live asisaid copy of SAFARI, so while the code changes won't go into effect over here until I have things working, some things might behave oddly.

Update (2005-Mar-13 18:50): All done. Now on to fixing things to use my time zone (central) rather than UTC.

Update 2 (2005-Mar-13 19:45): The time zone is now appropriate too. That was a bit more difficult. As it turned out, I had accidentally set my code to store posting dates in Eastern Time (well, or more properly, to use the server's localtime(), which I have set to Eastern for the sake of matching other U.S. servers, despite the fact that it is in the Central time zone). So all posts from the old program were in UTC/GMT, but all after around noon on December 24, 2004 were in EST. Therefore, I put together this little SQL query:

SELECT cid,FROM_UNIXTIME(UNIX_TIMESTAMP(gmt)+18000) FROM uninet_comments WHERE ('2004-12-24 12:00' <= gmt)

Afterwards, I exported the resulting output and did some regex magic to turn the insert statements into update statements:

s/INSERT INTO `uninet_comments` VALUES ((.*?), '(.*?)');/UPDATE uninet_comments SET gmt='$2' where cid='$1';/sgi;

Finally, I took the those SQL commands, fed them to MySQL. Rinse and repeat for the articles table.

After that, it was a simple matter to add the new time zone adjustments within SAFARI to take the GMT time information and convert it to Central Standard Time (UTC-6:00). Now, I just need to figure out how to implement daylight savings time support, so that posts and comments posted during DST will be UTC-5:00 without making standard time posts and comments switch as well.