Convert Unix-timestamp to MS Access Date/Time field.

I'm looking for code that will allow me to store a date in a table as a Unix-Timestamp, but display it and edit it in an MS Access form as human readable Date/Time.

Any help would be apreciated.

If I recall correctly, MS Access stores time fields as Julian dates. What format does Unix-Timestamp use?

0 Replies

DrewDad wrote:

If I recall correctly, MS Access stores time fields as Julian dates. What format does Unix-Timestamp use?

I guess it would have to be an unbound field with code behind it to convert to the unix date.

A Unix time stamp is seconds since '1970-01-01 00:00:00' UTC. For example, the time and date of 3/12/2006, 7:47:13 would be "1142149633".

This PHPBB (Able2Know) message board stores all of it's date and times in Unix-Timestamp.

0 Replies

Quote:

Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date.

It should be straight-forward to convert from Julian to Unix-Timestamp.

Subtract the Julian date of 1/1/1970 00:00:00 from your stored date. Then convert the result to seconds. (Multiply the result by 86400, the number of seconds in one day.)

0 Replies

And don't forget to adjust for the time zone.

0 Replies

True, that part is just a matter of mathematics. I'm having trouble with the syntax though. I'm not familiar with programming in Access. I suppose I will have to take some tutorials. I was hoping someone would just magically poop out the code for me. :wink:

0 Replies

Hello anyone who landed on this forum thread:

Unix Time Stamp records seconds since '1970-01-01 00:00:00'

Microsoft store a number representing the number of complete days since '1970-01-00 00:00:00' i.e. 1 = '1970-01-01 00:00:00'

So, Microsoft's date value for the beginning of the unix era '1970-01-01 00:00:00' is 25569

The number of seconds in 25569 days is:
25569*24*60*60
= 2209161600

The number of seconds in 1 day is:
24*60*60
= 86400
So in Access:

Code:HumanDate: Format(([unixdate]+2209161600)/86400,"dd/mm/yy hh:nn:ss")

Hope the readers of this thread find this post helpful.

I think of got this straight but obviously use at your own risk :-)

Tim Woolfson
EDIT: MODERATOR: DO NOT POST YOUR LINKS HERE

0 Replies

Well, I've long since figured this problem out, but thanks for the reply.

0 Replies

I'm just a visiting stranger, but I found this very helpful. It's exactly the answer I came looking for...

0 Replies