3

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

Alex.Barylski
  • 2,843
  • 4
  • 45
  • 68
  • Are your dates stored as unix timestamps or datetime? – user1191247 Apr 05 '12 at 14:14
  • How about http://excel.tips.net/T002051_Converting_UNIX_Date_Time_Stamps.html ? – Fionnuala Apr 05 '12 at 14:15
  • 1
    Unix Timestamps are in UTC. Is your code converting to your local timezone? 1335312000 is 2012/04/24 in the UK, but 2012/04/25 in the US. – Marcus Adams Apr 05 '12 at 14:37
  • I know the timestamps are stored as GMT - which I understand is the same thing as UTC? That being said, I have no idea how to enforce/check this in Access - it's a legacy system which were are integrating with new web-based systems. I shall google on setting timezones in Access EDIT | Co-worker raised a good point. Timezones in Access are probably pulled from local machine. mine is set to: UTC-06:00) Central Time (US & Canada) Using the following: TIME STAMP: 1335312000 DATE (M/D/Y @ h:m:s): 04 / 24 / 12 @ 7:00:00pm EST – Alex.Barylski Apr 05 '12 at 14:44

2 Answers2

2

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
0

If the date_promised field is of a DATETIME type you can use -

SELECT date_promised, DATE_FORMAT(date_promised, '%m/%d/%Y') AS date_promised2
FROM erp_workorder AS t1
WHERE id_primary = 73135;

If the date_promised field contains a unix timestamp you can use -

SELECT date_promised, DATE_FORMAT(FROM_UNIXTIME(date_promised), '%m/%d/%Y') AS date_promised2
FROM erp_workorder AS t1
WHERE id_primary = 73135;
user1191247
  • 10,808
  • 2
  • 22
  • 32
  • The dates are stored as unix timestamps not MYSQL DATETIME just a plain jane integer that format fine in PHP and similar but Access for some reason wants to add a day :( – Alex.Barylski Apr 05 '12 at 14:29
  • TO clarify...the tables are stored as MySQL tables with timestamps...Access application uses Queries/Views to bring the data into perspective - for lack of a better word. The SELECT statement of the queries MUST be done in Access not MySQL – Alex.Barylski Apr 05 '12 at 14:30
  • I thought you could force non standard queries in MS Access. If not, you could always create some views in MySQL that produce the desired output and then query the views from Access. – user1191247 Apr 05 '12 at 14:33
  • This is a last resort work around yes, ideally I want it done in Access. :( – Alex.Barylski Apr 05 '12 at 14:49