Convert from Unix time to Access Date/Time?

[Marty Christion]

Hi all,

I've just imported a large table that is Unix time for the dates, so the
fields are formatted as "Number" instead of "Date/Time".

For example, May 22, 2006 is 1148264725. I understand this is the number of
seconds from January 1, 1970. Are there any functions that can convert this
to a proper date/time?

 

[John Vinson]

Hi all,

I've just imported a large table that is Unix time for the dates, so the
fields are formatted as "Number" instead of "Date/Time".

For example, May 22, 2006 is 1148264725. I understand this is the number of
seconds from January 1, 1970. Are there any functions that can convert this
to a proper date/time?

DateAdd("s", [UnixTime], #1/1/1970#)

Testing it out:

?dateadd("s", 1148264725, #1/1/1970#)
5/22/2006 2:25:25 AM

John W. Vinson[MVP]

 

[Marty Christion]

Thanks John!

I'm kind of an Access noob, but I was able to use that equation in a query.
If anyone else has the same problem, here's what I did.

I created a query, and added all the fields I needed from the table,
including the Unix "date" field (unix_date).
In an empty column in the "design" view of the query, I entered this
equation in the "Field" field:

AccessDate: DateAdd("s",[unix_date],#1/1/1970 5:00:00 AM#)

Now when I run the query, I have a new field called AccessDate which shows
the correct Date/Time. I can use this for forms and reports as needed.




AccessDate: DateAdd("s",[problem_reportdate],#1/1/1970 5:00:00 AM#)

Hi all,

I've just imported a large table that is Unix time for the dates, so the
fields are formatted as "Number" instead of "Date/Time".

For example, May 22, 2006 is 1148264725. I understand this is the number
of
seconds from January 1, 1970. Are there any functions that can convert
this
to a proper date/time?

DateAdd("s", [UnixTime], #1/1/1970#)

Testing it out:

?dateadd("s", 1148264725, #1/1/1970#)
5/22/2006 2:25:25 AM

John W. Vinson[MVP]

 

[John Vinson]

Thanks John!

I'm kind of an Access noob, but I was able to use that equation in a query.
If anyone else has the same problem, here's what I did.

I created a query, and added all the fields I needed from the table,
including the Unix "date" field (unix_date).
In an empty column in the "design" view of the query, I entered this
equation in the "Field" field:

AccessDate: DateAdd("s",[unix_date],#1/1/1970 5:00:00 AM#)

Now when I run the query, I have a new field called AccessDate which shows
the correct Date/Time. I can use this for forms and reports as needed.

If you wish, you can add a new Date/Time field to the table, and run
an Update query updating it to this expression. Unless you're
routinely importing data from the Unix file, this may make your forms
and reports run faster since you won't need to call a function on
every row.

John W. Vinson[MVP]