Search code examples
javasqlitemillisecondsjulian-date

sqlite convert Jdn to unixepoch

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 ?????

Solution

  • 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