Unix Date/Time Stamp Conversions

Convert a Unix date/time stamp

Converting a Unix date/time stamp to a standard date and time format in an MS Access database query. As Google did not return the answer for a change and I have done a bit of coding in VB, I decided to insert the the following code into a new (VBA) module in the Access database:

Public Function sDate(UnixDateStamp As Double)
	sDate = DateAdd("s", UnixDateStamp, #1/1/1970#)
End Function

A Unix time stamp or date stamp is the number of seconds from 1/1/1970.
(So all the function does is add the timestamp seconds to 1/1/1970 and returns a "normal" date and time! )

Once the module is saved it is just a matter of passing the name of the field in the query that contains the Unix timestamp (in this case the field is called "unix_timestamp"), into the "sDate" function from within a new field in the query, as part of the function call:

Expr1: sDate([unix_timestamp])

Convert a "Normal" date

Converting a "Normal" date (dd/mm/yyyy) to a Unix date and time format in an MS Access database query. I recently had to construct the reverse of the previous process to solve another problem, so I created another module in the Access database that allows conversion of "normal" date/time to a Unix date and time format:

Public Function uDate(NormalDate As Date)
   uDate = DateDiff("s", #1/1/1970#, NormalDate)
End Function

This function then allowed me to set a "Criteria" on a Unix date/time number field as follows:

Between uDate([From Date:]) And uDate([Until Date:])

When the query is run the user can insert a "normal" date range into the 2 dialogue boxes that are presented. These dates are converted to Unix date format by the "uData" function and compared to the data being queried. The results returned are dates within the range entered by the user! Be aware the the last (Until) date in this process will not include results from the date entered as the function only converts to the start of the last day! I imagine that the following would work if you wish to include the results of the last day (but I have yet to try this...):

Between uDate([From Date:]) And (uDate([Until Date:] + (60*60*24)))

Thanks to Doug Parsons who used the above code and emailed me his correction... It is now reading as Doug suggests.