Home > General Articles, mysql > Session TimeZones and DateTime fields

Session TimeZones and DateTime fields

March 10th, 2010 Leave a comment Go to comments

Today I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone. Well we arrived at a conclusion that we could not do that on DateTime fields in MySQL. Alas we were having varchar(19) in the equivalent field in MsSQL, which was the outcome of an import from MySQL using a connection string and a procedure. We were at a dismay, and admitted defeat. Later while having lunch, I got enlightend about using the built in functions to do the methodical conversions.

Aaah.. this was not the usual magical experience I expected. Then again why should I expect something like that when it is not there in the specifications. Okay to cut the crap. We used

select id_workorder,'GMT',created_on from workorder where id_workorder = 1
Union
select id_workorder,'-8:00',DATEADD(s,datediff(s,'19700101',created_on) - 28800,'19700101') from workorder where id_workorder = 1
Union
select id_workorder,'+5:30',DATEADD(s,datediff(s,'19700101',created_on) + 19800,'19700101') from workorder where id_workorder = 1

for MSSQL and

select id_workorder,'GMT',created_on from workorder where id_workorder = 1
Union
select id_workorder,'-8:00',FROM_UNIXTIME(UNIX_TIMESTAMP(created_on) - 28800) from workorder where id_workorder = 1
Union
select id_workorder,'+5:30',FROM_UNIXTIME(UNIX_TIMESTAMP(created_on) + 19800) from workorder where id_workorder = 1

for MySQL.

Well this is going to be tough. I will need to write a couple of functions, and modify our code here and there, such that php will be using proper time_zone info and converting the datetime data choosen from the frontend to corresponding GMT before saving into the tables as a string. And I will need to write functions for both MsSQL and MySQL. Then do some juggling in our abstraction library to run these functions respectively.

Categories: General Articles, mysql Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

fifty − forty two =