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

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.

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.)

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

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:

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:



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

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

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

thank you for the helpfull formula.
Danyman_de (GER)

You solved me millions of headaches. THANK YOU!!!!!

btw,Microsoft's date value for the beginning of the unix era '1970-01-01 08:00:00' is what?

I've been looking for this conversion for ages!!! this helps me so much...

Now I just have to put it somewhere I can remember where it is... *gryn*

How do I write the formula for this to include timezone and daylight savings