SQLite Forum

strange strftime returns at julian day zero
Login

strange strftime returns at julian day zero

(1.1) By curmudgeon on 2020-08-17 16:52:06 edited from 1.0 [source]

sqlite> select strftime('%Y-%m-%d %H:%M:%f',0), strftime('%J','-0471-11-24 12:00:00.000'), strftime('%Y-%m-%d %H:%M:%f',1549358);

-471-11-24 12:00:00.000|1549358|-471-11-24 12:00:00.000

What am I doing wrong?

(2) By Donald Griggs (dfgriggs) on 2020-08-17 17:54:46 in reply to 1.1 [link] [source]

The beginning of the Julian day epoch is 4714 BC (Nov 24, noon Greenwich, Gregorian Proleptic calendar.)

From the datetime documentation, the "%Y" conversion is not defined for BCE years.

https://www.sqlite.org/lang_datefunc.html

%Y year: 0000-9999

(3) By curmudgeon on 2020-08-17 18:15:12 in reply to 2 [link] [source]

Thanks Donald.

(4.1) By Keith Medcalf (kmedcalf) on 2020-08-17 19:58:18 edited from 4.0 in reply to 1.1 [link] [source]

Nothing.

sqlite> select strftime('%Y-%m-%d %H:%M:%f',0), strftime('%J','-4713-11-24 12:00:00.000');
┌─────────────────────────────────┬───────────────────────────────────────────┐
│ strftime('%Y-%m-%d %H:%M:%f',0) │ strftime('%J','-4713-11-24 12:00:00.000') │
├─────────────────────────────────┼───────────────────────────────────────────┤
│ -471-11-24 12:00:00.000         │ 0                                         │
└─────────────────────────────────┴───────────────────────────────────────────┘

The %Y format specifier returns the leftmost 4 characters of the output format "%04d". Since the actual year value is -4713 CE, which will not, with the sign, fit in 4 characters, it is truncated on the right to 4 characters, or "-471". The input parser does not, however, have this problem, but does require that there be 4 digits in the year with an optional leading - sign.

Dates prior to January 1st, 1 BCE ('0000-01-01 00:00:00.000') cannot "round trip" the parser.

Note that the units for %Y is "year CE", and the - sign does not mean BCE. Everything before 1 CE is "off by one" since year 0 does not exist (that is 0 CE is 1 BCE).

(5) By curmudgeon on 2020-08-18 06:43:53 in reply to 4.1 [link] [source]

Thanks again Keith. Much clearer now.