Convert from Unix time to Access Date/Time?

  • Thread starter Thread starter Marty Christion
  • Start date Start date
M

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?
 
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?
Click to expand...

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]
 
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#)
John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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]
 
Back
Top