Converts dateTime value to the number of days since January 1, 1970 UTC. This is the format used by Excel.
I tested this survey:
type
name
label
calculation
calculate
calc
decimal-date-time(today())
note
mycalc
${calc}
And the note displayed the number 17821.166666666668 but if you plug that number into Excel and change the cell format to a date, it displays as 10/15/1948. The documentation is wrong? I'm not interpreting the documentation correctly? Something else?
I believe the documentation is probably incorrect - Excel dates are from 1900, not 1970:
Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time....
Excel can also treat the date portion of a serial date as the number of days since 1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for compatibility with the Macintosh system.
So 10/15/1948 = 01/01/1900 plus 1781 days sounds about right... Probably just need to amend the ODK documentation accordingly.
Whereas Jan 01 1970 is the epoch date for Unix-based computers (ie a real OS... )
Hmm, that would be my bad! I think it's helpful for users to know it's the format used by Excel but off by 70 years but maybe that doesn't need to be in the spec at all. I think I meant it as a reminder of why we do the fractional day piece (which should convert to a time if you make the cell a dateTime).
We have some documentation about doing Excel-based conversions in the context of the enumerator log: https://docs.opendatakit.org/form-audit-log#timestamps. Perhaps we can have something similar in the user-facing docs for decimal-date-time() and drop any mention of Excel in the spec.
And in case you are wondering why "1904? huh? WTF!", here's a bit o' computer history explaining why:
The original Mac development team chose midnight, January 1, 1904, as the start of the Mac calendar in part because it's mathematically convenient to have a calendar system start on a leap year, which 1900 was not.
ah, 70 years off, who cares... Indeed I believe it was Mr ISO8601 himself (aka @ggalmazor) who said "consistency is far more important that correctness"
)
)