How are datetimes returned from createReplica when dataFormat=sqlite?

[samhooper]

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 = 2451544.875 # obtained by entering a datetime value in Survey123 Jan 1, 2000 12:00 AM
m = 365.25 # slope
b = y2000 - m * 2000 #y-intercept
print(1970 * m + b) 
# prints 2440587.375‍‍‍‍. Note the .375, not .875 returned by createReplica‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.  

    # Python (really Unix) uses 1970-1-1 00:00:00 as the epoch (i.e., reference timestamp).
    TIMESTAMP1970 = 2440587.875 # Equivalent value of the Python epoch in a createReplica SQLite DB
    agol_datetime = datetime.fromtimestamp((agol_timestamp - TIMESTAMP1970) * 60 * 60 * 24)
    timezone = pytz.timezone('US/Alaska')
    # if agol_datetime were actually in UTC, utcoffset should be added, because it's already negative
    local_datetime = agol_datetime - timezone.utcoffset(agol_datetime)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

[RandyBurton · Accepted solution]

SQLite can store dates as text, real or integer. When you create a replica SQLite geodatabase, the date/time format is real.  It counts days from November 4714 BC.  You can use sqlite3 to query the database and convert the dates:

import sqlite3 as lite
from datetime import datetime
from dateutil import tz
import time, re

gdb = r'C:\path\to\date_time_test.geodatabase'
field = 'EditDate'
table = 'Date_Time_Test'

query = "SELECT {0}, date({0}), time({0}) FROM {1}".format(field, table)
# print query

con = lite.connect(gdb)
with con:    
    
    cur = con.cursor()    
    cur.execute(query)
    
    rows = cur.fetchall()

    print "{}\t{}".format('EditDate','DateTime')

    for row in rows:
        dt = "{} {}".format(row[1],row[2])
        lt  = datetime.strptime(dt, '%Y-%m-%d %H:%M:%S').replace(tzinfo=tz.gettz('UTC')).astimezone(tz.tzlocal())
        ts = time.strftime('%Y-%m-%d %H:%M:%S  %Z', time.localtime(time.mktime(lt.timetuple())))
        print "{}\t{}".format(row[0], ts[:21]+re.sub('[^A-Z]','',ts[21:]))

# ======================
print # another test example

EditDate = 2458795.34270833 # submitted a record at 11:13 AM (on Nov 7, 2019) Alaska Standard Time
print "EditDate: {}".format(EditDate)

con = lite.connect(":memory:")
dt = list(con.execute("select date("+str(EditDate)+") || ' ' || time("+str(EditDate)+")"))[0][0]
lt  = datetime.strptime(dt, '%Y-%m-%d %H:%M:%S').replace(tzinfo=tz.gettz('UTC')).astimezone(tz.tzlocal())
ts = time.strftime('%Y-%m-%d %H:%M:%S  %Z', time.localtime(time.mktime(lt.timetuple())))
print ts[:21]+re.sub('[^A-Z]','',ts[21:])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The second part of the script will convert an EditDate date of 2458795.34271 to the string 2019-11-07 11:13:30  AST.  This is from your first question.

For more information:

SQLite Date and Time Functions

Tutorial: SQLite Date and Time

Calculating julian date in python

[RandyBurton]

Here's an additional script example using the sqlite3 module to work with the realdate and unixepoch formats.  SQLite functions can convert a date/time to local date/time without the datetime module; see lines 17 and 33 below.

import sqlite3

con = sqlite3.connect(":memory:")

# sqlite function datetime(timestring, modifier, ...)
# timestring in one of several formats including 'YYYY-MM-DD HH:MM:SS'
# modifier 'localtime' assumes timestring is in UTC and displays local time
# see: https://sqlite.org/lang_datefunc.html

print 'realdate format'
query = "select julianday('now')" # output realdate format
jd = list(con.execute(query))[0][0]
print jd # 2458798.48683
query = "select date({0}) || ' ' || time({0})".format(jd) # utc time
dt = list(con.execute(query))[0][0] 
print dt # 2019-11-10 23:41:02
query = "select datetime(date({0}) || ' ' || time({0}),'localtime')".format(jd) # local time
dt = list(con.execute(query))[0][0]
print dt #  2019-11-10 14:41:02
print
query = "select julianday('2019-11-07 11:13:30-09:00')" # output realdate format
jd = list(con.execute(query))[0][0]
print jd # 2458795.34271
print

print 'unixepoch formt'
query = "SELECT (julianday('now') - 2440587.5)*86400.0" # output uxixepoch
ue = list(con.execute(query))[0][0]
print ue # 1573429262.01
query = "select datetime({0},'unixepoch')".format(ue)
dt = list(con.execute(query))[0][0]
print dt # 2019-11-10 23:41:02
query = "select datetime({0},'unixepoch','localtime')".format(ue)
dt = list(con.execute(query))[0][0]
print dt # 2019-11-10 14:41:02
print‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[samhooper]

Thanks for your response, Randy. Makes sense now that I understand that createReplica is storing the dates as Julian dates. 

The reason why it seemed like the timezone was applied in the wrong direction is because the Julian day equivalent of 1970-1-1 00:00:00 is actually 2440587.5 (as you use in line 27 of the code above), but from my stabbing in the dark approach to figuring all of this out, I had equated the Unix epoch with 2440587.875 or 1970-1-1 09:00:00. 

To tie up the other loose end from Problem 1 of my initial question, the Julian calendar starts at noon (of the previous day, see https://sciencing.com/calculate-julian-date-6465290.html ) so you have to add 0.5 (a half of a day) before manually calculating a Gregorian calendar date.

For anyone looking for a simple Python solution, this should work for all dates after Jan 1, 1970. Albeit, using sqlite3 as Randy suggests is more robust and will work for any datetime.

import pandas as pd
from datetime import datetime, timedelta

def julian_date_to_datetime(julian_date):

    JULIAN_UNIX_EPOCH = 2440587.5 # 1970-1-1 00:00:00 as a Julian date
    if pd.isna(julian_date) or julian_date < JULIAN_UNIX_EPOCH:
        return pd.NaT
    try:
        epoch_timestamp = round((julian_date - JULIAN_UNIX_EPOCH) * 60 * 60 * 24) # number of seconds since Unix epoch
        return datetime(1970, 1, 1) + timedelta(seconds=epoch_timestamp)
    except:
        return pd.NaT


datetime_field = 'CreationDate'
df[field] = pd.to_datetime([julian_date_to_datetime(jd) for jd in df[datetime_field]])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍