jeudi 24 décembre 2015

Start of day in localtime from UTC

I have a table of events containing the start time in UTC (unixepoch), e.g.

sqlite> SELECT name, startUtc, datetime(startUtc, 'unixepoch') AS datetime FROM events;
name        startUtc    datetime
----------  ----------  -------------------
aaa         1451008800  2015-12-25 02:00:00
bbb         1451039400  2015-12-25 10:30:00

These values will be returned in a Cursor. However, I'd like to precompute the day each event falls on with regard to the user's local time zone (which for me is GMT-08:00). Basically I want a timestamp for the appropriate day, with all the more-granular fields (hours, minutes, etc) truncated. Here's what my query looks like along with the results:

sqlite> SELECT name, startUtc, strftime('%s', startUtc, 'unixepoch', 'localtime', 'start of day') AS localDay from events;
name        startUtc    localDay
----------  ----------  ----------
aaa         1451008800  1450915200                                                            
bbb         1451039400  1451001600

Sanity check:

sqlite> SELECT datetime(1450915200, 'unixepoch');
2015-12-24 00:00:00  

This all seems fine until I use the localDay value for event "aaa" in a Calendar object.

long localDay = cursor.getLong(cursor.getColumnIndex("localDay"));
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(localDay * 1000); // adjust bc sqlite returns seconds

int day = calendar.get(Calendar.DAY_OF_MONTH); // returns 23, not 24

Essentially the java Calendar interprets the timestamp as 2015-12-23 16:00:00 instead of 2015-12-24 00:00:00, and I end up with the wrong day for that event.

What am I missing here?

Aucun commentaire:

Enregistrer un commentaire