0

I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers.

How do you process these as if they were DATETIME in SQL queries?

If it simply can't be done through SQL and must instead be done through a programming/scripting language, what maths/process is required to transform these values into human-readable dates and times?

Example values: -1358667448, -1358667348, -1358667248

The data was captured during the period 2006-2019 so the timestamps will be within that time frame.

  • Does this page help? support.microsoft.com/en-us/help/210276/… – Philᵀᴹ Oct 7 at 15:29
  • Maybe a Unix Timestamp? -1358667248would beGMT: Sunday, 12. December 1926 16:25:52 – ComputerVersteher Oct 7 at 15:48
  • @ComputerVersteher I've updated the question to reflect the data time frame of between 2006 and 2019. – richhallstoke Oct 7 at 16:29
  • @phil™ - The date is definitely not referring to timestamps prior to December 30, 1899. I've updated the question to reflect the time frame of 2006-2019. Thank you. – richhallstoke Oct 7 at 16:33
  • 1
    @JamesJenkins No, Access doesn't show the values as dates/times but shows them as the negative numbers as I've included in the question. I know from the column names though that these are timestamps. In the application that uses this database (we don't have the source code) the dates/times appear as human readable. The values I included as examples are from measurements taken automatically at regular intervals, possibly every 10 seconds or every minute. – richhallstoke Oct 8 at 13:43
0

Per Your comment the only place the values show as time stamps is in the application. You don't have access to the applications code.

The only possible solution, is to get a handful of actual time values shown in the application and relate them to values in the field, than figure how the difference is calculated. Then you will need to recalculate the values and add them to your database.

I strongly suggest that you do not replace the source data. Possibly add columns to the table with the correctly formatted dates.

0

I had originally expected it might be following some kind of common standard for which asking the community might have shed some light as to which standard format etc, however after further investigation it looks like the timestamps may in fact be the number of seconds since the Unix Epoch (01-Jan-1970 00:00:00), but with a minus symbol added for some unknown reason. Whilst currently unverified, based on answers from the Epoch Converter online tool:

  • 1358667448 could be 2013-01-20 07:37:28 UTC-0
  • 1358667348 could be 2013-01-20 07:35:48 UTC-0
  • 1358667248 could be 2013-01-20 07:34:08 UTC-0

If anyone knows why the minus symbol might have been added please leave a comment, I would be interested to know. Thank you.

In terms of processing the values as dates/times in SQL as originally asked, there are numerous examples on the Epoch Converter website for the different database engine variants.

  • Why are you guessing on what the values could be? Look in the application and see what dates are displayed for specific data entries. – James Jenkins Oct 9 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.