Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Convert from Unix time to Access Date/Time?

Discussion in 'Information Technology' started by Marty Christion, Jul 28, 2006.

  1. Marty Christion

    Marty Christion
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    Sikhconvert Joins Sikh Philosophy Network! New SPN'ers Aug 28, 2016
    Jehovah's Witnesses Are Out And Converting! Blogs Jul 31, 2016
    Islam Ishna Has Converted To Islam Interfaith Dialogues Apr 1, 2016
    A Christian Missionary tried to convert me. Blogs Oct 22, 2015
    "Convert's Corner" - Ideas for Retitle? Convert's Corner Oct 21, 2015

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 10 May 2006 17:19:50 -0700, "Marty Christion" <anon@yahoo.com>
    wrote:

    >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]
     
  4. Marty Christion

    Marty Christion
    Expand Collapse
    Guest

    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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:rmd56252nlgfvslc7pkkf88epdkbr6il21@4ax.com...
    > On Wed, 10 May 2006 17:19:50 -0700, "Marty Christion" <anon@yahoo.com>
    > wrote:
    >
    >>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]
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 11 May 2006 09:56:14 -0700, "Marty Christion" <anon@yahoo.com>
    wrote:

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

Share This Page