Converting Unix Timestamp

[Brian Kerr]

Hi,

I am importing data that has a Unix time stamp that I need
to convert to normal date and time in Access.

For example the Raw Arrival (Unix Timestamp - 1094154395)
should equate to a date of 09/03/2004 and time of 00:07:46

I would appreciate any assistance any one can provide.
If there is no way to convert this in Access then I would
need to look at doing a conversion in Excel then importing
the data from Excel.

Brian Kerr
Technical Services Manager

 

[Albert D. Kallal]

The unix date/time is the number of seconds from

Jan 01, 1970.

So, we should be able to simply add seconds to this date.

In the debug window, we get:

? dateadd( "s",1094154395,#01/01/1970#)
09/02/2004 19:46:35

I notice that you got Sept 03, and the above gives me sept 02

However, the above does give 07:46 for the time (pm)

So, you can leave you data as is..and convert..or run a update query to send
the values to a datetime field.

Check your example...as we do seem to get one day off....

 

[Guest]

This is excellent, thank you very much.
I was told you could not convert the unix time stamp.

As for the differences in date I have checked the example
and it is correct, I suppose it would not have anything to
do with time zones???

I thank you for your help.

Brian Kerr
Technical Services Manager

 

[Albert D. Kallal]

This is excellent, thank you very much.
I was told you could not convert the unix time stamp.

As for the differences in date I have checked the example
and it is correct, I suppose it would not have anything to
do with time zones???

Perahps mid night! (ie: >>Jan 01, 1970....


1 second after mid night on Jan 01 becomes Jan 02

If you are sure of the resulting date being Sept 03, then just use:

? dateadd( "s",1094154395,#01/02/1970#)

So, just try a few test dates to make sure this works...

So, it looks like we just start from Jan 02....