Is there a reliable way to convert unzoned datetime into UNIX time_t?

Question

I need to convert a database column in a (MSSQL) database table into UNIX time_t. The problem is that this column does not have a timezone and some dates are from GMT (+0000) and some from BST (+0100). I am working on UK times.

Is there a reliable way to convert unzoned datetime into UNIX time_t? I'm currently using

SELECT *,DATEDIFF(second,'1970-01-01',event_time)

But this seems to be subject to the current timezone, not that applicable to the date.

I don't mind if I do the conversion in SQL or code. I'm working in Perl.

Answer 1

A UNIX timestamp is UTC without daylight savings time. So if you can't tell if a date is GMT or BST, you can't convert it to a UNIX timestamp.

Answer 2

If you mean that the times are BST in summer and GMT in winter then there is a timezone implicit in the column, and it's not "two different timezones", it's one timezone, called Europe/London in the Olson DB or GMT0BST,M3.5.0/1,M10.5.0/2 in POSIX TZ format. You should easily be able to convert these times using the DateTime module or even clever use of the POSIX functions, except for the ambiguous hour around the fall transition, which can't be resolved unless you store an indication of whether BST was or was not in effect at the time.

Example using DateTime:

use DateTime::Format::MSSQL;
# In reality you would get this from the DB.
my $input = "2009-11-30 16:13:18.123";
my $dt = DateTime::Format::MSSQL->parse_datetime($input);
$dt->set_time_zone("Europe/London");
print "Unix time is ", $dt->epoch, "\n";

Answer 3

With SQL 2008 there is a new date time type: datetimeoffset that does retaint timezone info and thus can be used to susbtract diff from Unix epoch. But this requires upgrade to SQL 2008, revisiting your schema and changing the column type and updating retroactively all existing data to set the proper timezone.

If you cannot upgrade to SQL 2008 then I recommend storing your date time info as UDT always, which implies convert it in the client first before inserting it in the DB. Displaying the data to the user in the appopiate local time should be, again, done in the client.