Revisit Date.parse, time zone, dst, and sqlite issues

I thought Date.parse() was very easy, until some time zone and DST (daylight saving time) issues came to me today.

First problem, missing time zone means UTC

DateTime.parse(“2012-04-19 12:12:12”) is default to UTC, I need find out the correct time zone. OK, MDT for now. But, what if DST finished?

That means I can’t hard code MDT in date string.

Need to find out a way to check if date_to_parse is in DST period or not.

Assuming the machine in which the code is running on is at the same time zone as the date_to_parse.

        date_s = "2012-04-19 12:12:12"
        date = DateTime.parse("#{date_s}")
        time_zone = Time.local(date.year, date.month, ? "MDT" : "MST"
        puts DateTime.parse("#{date_s} #{time_zone}")

OK, it works very well. I don’t have to worry about to change the time zone back when Winter is coming.

Second issue, date in sqlite3.

Sqlite3 doesn’ t have a datetime internal date type. It convert it to string or integer.

So when saving date into sqlite3, right way should be either convert date to integer (unix epoch),


or convert it to string WITH TIME ZONE.

date.strftime("%Y-%m-%d %H:%M:%S %Z")

Third one, comparing date in sqlite3

Remember no date_diff in sqlite3, date is either string (not compare friendly) or integer.

select date, strftime('%s',date) as orig_date,strftime('%s','#{"%Y-%m-%d %H:%M:%S %Z")}') as new_date from occurrence
where abs(orig_date - new_date) < 3600 * 10


One thought on “Revisit Date.parse, time zone, dst, and sqlite issues

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s