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, date.day).isdst ? "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),

Time.parse(date_s).to_i

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','#{occurrence.date.strftime("%Y-%m-%d %H:%M:%S %Z")}') as new_date from occurrence
where abs(orig_date - new_date) < 3600 * 10