sqlite convert Jdn to unixepoch

Question

I have a column of date in jdn format. I want to migrate my date format from jdn to Java Calendar.getTimeInMillis() that is equal to sqlite unixepoch.

here is sample migration code.

ALTER TABLE  TABLE_TIMES  ADD COLUMN  millies INTEGER
UPDATE TABLE_TIMES SET millies = ????
-- ALTER TABLE for DELETE COLUMN jdn

how i can convert jdn to unixepoch. what should i use instead of ?????

SQLite's `unixepoch` is not milliseconds. – CL. Jun 25 '14 at 08:00 — CL., Jun 25 '14 at 08:00
millisecond is not important, we can put 000 to the end. – mrd abd Jun 25 '14 at 08:02 — mrd abd, Jun 25 '14 at 08:02

score 2 · Accepted Answer · answered Jun 25 '14 at 08:17

SQLite's built-in date/time functions already support Julian day numbers. To output a unixepoch value, use the %s format of strftime. To convert from seconds to milliseconds, multiply by 1000:

UPDATE Table_Times SET millies = strftime('%s', jdn) * 1000

sqlite convert Jdn to unixepoch

1 Answers1