Converting timestamps to human readable in MS Access

Question

I have dozens of MySQL tables linked in a MS Access program. There are also a dozen or so Queries which pull these tables togather and provide data in a human fashion, especially converting timestamps to mm/dd/yyyy format.

I have tested the timestamps http://www.unixtimestamp.com/index.php) but the following code below produces the date: 4/25/2012 which is not correct it should be 4/24/2012

SELECT date_promised, DateAdd('s', 1335312000, #1/1/1970#) AS date_promised2 FROM erp_workorder AS t1 WHERE id_primary = 73135;

What is going on here?

Regards, Alex

Answer 1

The DateAdd() expression in your query asks for the Date/Time equivalent of the Unix timestamp as UTC time.

? DateAdd("s", 1335312000, #1/1/1970#)
4/25/2012 

Expand the format to display the time explicitly.

? Format(DateAdd("s", 1335312000, #1/1/1970#), "yyyy-mm-dd hh:nn:ss")
2012-04-25 00:00:00

When you plug your timestamp into the box on that web page, then click the "Convert" button, it gives you this value:

04 / 24 / 12 @ 7:00:00pm EST

Using the earlier format ...

2012-04-24 19:00:00

So now compare these two different representations of the same moment in time.

2012-04-25 00:00:00 (UTC)
2012-04-24 19:00:00 (EST)

If you want your query to display the UNIX timestamp with an offset for any time zone, apply that conversion to make it so.