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