TL;DR How can I convert datetime values returned in an SQLite DB from createReplica to Unix Epoch time (i.e., number of seconds after Jan 1, 1970)?
I'm using createReplica from the ArcGIS REST API to return data from a feature service as an SQLite database. The feature service is tied to a Survey123 survey, and the datetime values returned are very strange. For instance, I just submitted a record at 11:13 AM (on Nov 7, 2019) Alaska Standard Time (-9 hour UTC offset), and the CreationDate is 2458795.34270833. I also tested different dates in a datetime question of the survey and got the following (note that I also entered these from a computer with local time in AKST):
- Jan 1, 2000 12:00 AM: 2451544.87
- Jan 1, 1970 12:00 AM: 2440587.875
- Jan 1, 1900 12:00 AM: 2440587.875
Problem 1: It seems like these are decimal datetimes, but the epoch used to calculate them is not clear at all. I tried to figure it out by calculating the equation of the line that would produce these values with the following Python code snippet. However, when I check this equation against the 1970 value actually returned by createReplica, it's slightly off:
y2000 <SPAN class="operator token">=</SPAN> <SPAN class="number token">2451544.875</SPAN> <SPAN class="comment token"># obtained by entering a datetime value in Survey123 Jan 1, 2000 12:00 AM</SPAN>
m <SPAN class="operator token">=</SPAN> <SPAN class="number token">365.25</SPAN> <SPAN class="comment token"># slope</SPAN>
b <SPAN class="operator token">=</SPAN> y2000 <SPAN class="operator token">-</SPAN> m <SPAN class="operator token">*</SPAN> <SPAN class="number token">2000</SPAN> <SPAN class="comment token">#y-intercept</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1970</SPAN> <SPAN class="operator token">*</SPAN> m <SPAN class="operator token">+</SPAN> b<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># prints 2440587.375. Note the .375, not .875 returned by createReplica</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Problem 2: Unless I'm calculating this incorrectly, this would mean that the epoch is sometime in the year 4711 BC (y2000/365.25 - 2000). This seems far too random to be correct.
Problem 3: I assume that Survey123 stores times in some standard timezone, but I would expect it to be UTC. The following code seems to be working to convert the time returned by createReplica to a Python datetime, but the UTC offset seems to be applied the wrong way to the createReplica-given timestamp (i.e., it's subtracted from the time entered rather than added). Also, if datetimes are actually stored and returned in UTC time, that's inconsistent with how dates are returned if the dataFormat is either "json" or "filegdb", which is in local time.
<SPAN class="comment token"># Python (really Unix) uses 1970-1-1 00:00:00 as the epoch (i.e., reference timestamp).</SPAN>
TIMESTAMP1970 <SPAN class="operator token">=</SPAN> <SPAN class="number token">2440587.875</SPAN> <SPAN class="comment token"># Equivalent value of the Python epoch in a createReplica SQLite DB</SPAN>
agol_datetime <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>fromtimestamp<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>agol_timestamp <SPAN class="operator token">-</SPAN> TIMESTAMP1970<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="number token">60</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="number token">60</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="number token">24</SPAN><SPAN class="punctuation token">)</SPAN>
timezone <SPAN class="operator token">=</SPAN> pytz<SPAN class="punctuation token">.</SPAN>timezone<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'US/Alaska'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># if agol_datetime were actually in UTC, utcoffset should be added, because it's already negative</SPAN>
local_datetime <SPAN class="operator token">=</SPAN> agol_datetime <SPAN class="operator token">-</SPAN> timezone<SPAN class="punctuation token">.</SPAN>utcoffset<SPAN class="punctuation token">(</SPAN>agol_datetime<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Even though this code seems to work for the limited dates I've tested it on, there are far too many uncertainties for me to trust that it will work in all cases. Any insight into how I can accurately and reliably convert these datetimes would be much appreciated.
