CODEWITHSUNDEEP
androidsqlite
broadbear
broadbear

Reputation: 674

sqlite rounding 'error'

I'm using SQLite on and Android device.

I am attempting to convert a timestamp to a julian day, and round down the julian day by casting the result to an integer. For some reason, 2456902.0 is being rounded to 2456901. (I get the same result with the 'round' function as well)

I do need a workaround, but I would also like an explanation as to why this is happening if anyone has one.

My code and results are listed below:

cast(julianday((c.ts/1000), 'unixepoch') as int) as day, 
julianday((c.ts/1000), 'unixepoch') as jd

When ts = 1409564846705, jd = 2456902.0, day = 2456901 (datetime is Mon Sep 01 04:47:26 CDT 2014)

When ts = 1409631153881, jd = 2456902.8, day = 2456902 (datetime is Mon Sep 01 23:12:33 CDT 2014)

I am trying to convert the timestamp to julian days to group records by day, but some data is falling into the wrong day (as you can see above, two records will be produced if I group by day, but the dates are part of the same day). I've resorted to: strftime('%d', datetime((c.ts/1000), 'unixepoch', 'localtime')) as day. The problem with this is that if I query for more than a month, there will be duplicate 'day of month's. Is there a better way to do this?

Upvotes: 2

Views: 280

Answers (1)

CL.
CL.

Reputation: 180080

Julian day numbers have integer values at noon, so the start of a day is halfway between integers:

> SELECT julianday(1409631153881/1000, 'unixepoch', 'start of day');
2456902.5

It does not make sense to round Julian day numbers to integers unless you define precisely whether you want the previous or the next noon.

If you want just to group by the day, convert the value into a date string:

date(c.ts / 1000, 'unixepoch', 'localtime')

If you want a value that can be converted into a number, combine the year and the day of the year:

cast(strftime('%Y%j', c.ts / 1000, 'unixepoch', 'localtime') as int)

Alternatively, just divide the timestamp by the number of milliseconds in a day, but then you need to substract the proper offset of the timezone.

Upvotes: 4

Related Questions