If I have a column of type INTEGER or REAL, how do I determine if the value returned from a query represents a datetime (in time since epoch, julian value, etc.) that should to be converted to something that looks like a datetime with one of the built-in functions OR if it is just some random number that should be treated as an int or double?

Thank you in advance,

Dave Ritter

To be clear on my use case: I have an application that is executing queries against tables and it does not necessarily know the schema of the results coming back. I need to convert each value returned by the database into an appropriate local type (one of which is a DateTime representation).

If I have a column of type INTEGER or REAL, how do I determine if the value returned from a query represents a datetime (in time since epoch, julian value, etc.) that should to be converted to something that looks like a datetime with one of the built-in functions OR if it is just some random number that should be treated as an int or double?

Without knowing the human-applied intent of the schema, which your reply says isn't the case, you cannot possibly generically know whether any given number is intended to be a timestamp or not.

Like Stephen says, the only way to do so is to ask the person who designed the database which column(s) contain "epoch offsets", what epoch and unit was chosen by that person, so that you can "convert" the "epoch offset" to a presentation that makes your eyes happy.

Possible (common) epoch dates include (though any date could be picked as the epoch date):

#JDEPOCH  = datetime(-4714, 11, 24, 12,  0,  0, tzinfo=utc) # Julian Epoch
NETEPOCH  = datetime(    1,  1,  1,  0,  0,  0, tzinfo=utc) # Microsoft.NET Epoch
LILEPOCH  = datetime( 1582, 10, 15,  0,  0,  0, tzinfo=utc) # Lilian Epoch
ANSIEPOCH = datetime( 1601,  1,  1,  0,  0,  0, tzinfo=utc) # ANSI Epoch
RJDEPOCH  = datetime( 1858, 11, 16, 12,  0,  0, tzinfo=utc) # Reduced Julian Day Epoch (JD-2400000)
MJDEPOCH  = datetime( 1858, 11, 17,  0,  0,  0, tzinfo=utc) # Modified Julian Day Epoch (JD-2400000.5)
OLEEPOCH  = datetime( 1899, 12, 30,  0,  0,  0, tzinfo=utc) # OLE Epoch
NTPEPOCH  = datetime( 1900,  1,  1,  0,  0,  0, tzinfo=utc) # NTP Epoch
UNIXEPOCH = datetime( 1970,  1,  1,  0,  0,  0, tzinfo=utc) # Unix Epoch
GPSEPOCH  = datetime( 1980,  1,  6,  0,  0,  0, tzinfo=utc) # GPS Epoch

Some of the possible "epoch offset units" are:

seconds and fractions thereof (tenths, hundredths, milliseconds, etc)
days
weeks
fortnights
calendar months
lunar months
years
decades

and as many others as you can dream up. There is no reason why one might not store a value mesearued in "venusian revolutions since the lilian epoch" if that results in a number meaninful to the problem at hand (ie, for which the data storage system was designed to store data).

Without this information, you can "guess" and you mayhaps guess correctly (or, more likely, not). The only way to be sure is to read the documentation description of the data item contents -- or ask the person who decided to "put something in there" what they put.

In the recent past, I have used the following units:

calendar month since the ANSI epoch
days since NTP epoch
days since the .NET epoch (a Rata Die daystamp)
interval (6 minute) since the ANSI epoch
interval (5 minute) since the Unix epoch

It all depends on the application and what it needs/wants.
What the user wants to see/input is immaterial to the storage layer.