Julian Day to ISO 8601 string in SQLite

Question

I have a table that stores date/time values as julian days in SQLite (using the julianday() function). I can't seem to figure out how to convert them back to ISO 8601 style strings (YYYY-mm-ddThh:m:ss.sss) when I read them?

score 7 · Accepted Answer · answered Sep 30 '11 at 14:47

7

Just feed the Julian day number to the datetime function:

A time string can be in any of the following formats:
[...]
12. DDDDDDDDDD
[...]
Format 12 is the Julian day number expressed as a floating point value.

So datetime(julianday_output) goes in the opposite direction as the julianday function:

sqlite> select datetime(julianday(current_timestamp)) as dt_from_jd, current_timestamp as dt;
dt_from_jd          | dt
2011-09-30 14:46:52 | 2011-09-30 14:46:52

answered Sep 30 '11 at 14:47

mu is too short

426,620
70
833
800

Thanks! Don't know how I missed that. – ForeverLearning Sep 30 '11 at 15:24
1

This isn't technically 8601 though, as 8601 requires a `T` where sqlite prints a space. – rcorre Aug 02 '23 at 21:19
1

@rcorre True, not sure how I missed that. If you need strict ISO8601 then `strftime('%Y-%m-%dT%H:%M:%S', julianday())` – mu is too short Aug 04 '23 at 02:43

score 2 · Answer 2 · answered Sep 30 '11 at 14:31

2

Have you tried strftime? http://www.sqlite.org/lang_datefunc.html

answered Sep 30 '11 at 14:31

Pateman

2,727
3
28
43

I did see that but it wasn't immediately obvious to me how to make the opposite conversion. I thought %J operates on a string and returns a julian day number (as in SELECT strftime('%J', 'ISO_8601') – ForeverLearning Sep 30 '11 at 14:43

Julian Day to ISO 8601 string in SQLite

2 Answers2