Converting epoch time to "date/time"

Discussion in 'NZ Computing' started by Marshall Barton, Feb 2, 2004.

  1. Matthew Poole wrote:

    >I'm trying to report against a .mdb produced by third-party software.
    >The software keeps dates in epoch (seconds since midnight on a
    >particular date (not sure what date, either. Need to contact software
    >mfr)) format, and I'm looking for a way to convert that to a date/time
    >stamp.
    >There doesn't appear to be a way to do this using field formats within
    >the report. Does anyone have some code for making the conversion? Or
    >know of a way to trick the report into doing it itself?


    Sounds like they may be Unix type dates. Try this kind of
    expression:

    DateAdd("s", epochdate, #1/1/1970#)

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Feb 2, 2004
    #1
    1. Advertising

  2. I'm trying to report against a .mdb produced by third-party software.
    The software keeps dates in epoch (seconds since midnight on a
    particular date (not sure what date, either. Need to contact software
    mfr)) format, and I'm looking for a way to convert that to a date/time
    stamp.
    There doesn't appear to be a way to do this using field formats within
    the report. Does anyone have some code for making the conversion? Or
    know of a way to trick the report into doing it itself?

    I'm using Access 2002.

    TIA.

    --
    Matthew Poole Auckland, New Zealand
    "Veni, vidi, velcro...
    I came, I saw, I stuck around"

    My real e-mail is mattATp00leDOTnet
     
    Matthew Poole, Feb 2, 2004
    #2
    1. Advertising

  3. On Mon, 02 Feb 2004 19:52:53 GMT, (Matthew Poole)
    wrote:

    >I'm trying to report against a .mdb produced by third-party software.
    >The software keeps dates in epoch (seconds since midnight on a
    >particular date (not sure what date, either. Need to contact software
    >mfr)) format, and I'm looking for a way to convert that to a date/time
    >stamp.
    >There doesn't appear to be a way to do this using field formats within
    >the report. Does anyone have some code for making the conversion? Or
    >know of a way to trick the report into doing it itself?
    >
    >I'm using Access 2002.
    >
    >TIA.


    You could try this;

    date=((x+(365*70+19)*86400)/86400)-0.41667
     
    Steve Colebrooke, Feb 3, 2004
    #3
  4. Matthew Poole wrote:

    >In article <>, Marshall Barton <> wrote:
    >>Matthew Poole wrote:
    >>

    >*SNIP*
    >>Sounds like they may be Unix type dates. Try this kind of
    >>expression:
    >>
    >> DateAdd("s", epochdate, #1/1/1970#)
    >>

    >I take it that "epochdate" is the date stored in the database, and I use
    >this function as the ControlSource for the field that I'm returning the
    >date into?


    Yes, that is correct (with an = sign in front of it).

    Or, if epochdate is a variable, you could use that in a VBA
    expression.

    Or, you could even use it in a calculated field in a query.
    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Feb 3, 2004
    #4
  5. In article <>, Marshall Barton <> wrote:
    >Matthew Poole wrote:
    >

    *SNIP*
    >Sounds like they may be Unix type dates. Try this kind of
    >expression:
    >
    > DateAdd("s", epochdate, #1/1/1970#)
    >

    I take it that "epochdate" is the date stored in the database, and I use
    this function as the ControlSource for the field that I'm returning the
    date into?

    --
    Matthew Poole Auckland, New Zealand
    "Veni, vidi, velcro...
    I came, I saw, I stuck around"

    My real e-mail is mattATp00leDOTnet
     
    Matthew Poole, Feb 3, 2004
    #5
  6. On Tue, 03 Feb 2004 00:00:22 -0600, Marshall Barton
    <> wrote:

    >Matthew Poole wrote:
    >
    >>In article <>, Marshall Barton <> wrote:
    >>>Matthew Poole wrote:
    >>>

    >>*SNIP*
    >>>Sounds like they may be Unix type dates. Try this kind of
    >>>expression:
    >>>
    >>> DateAdd("s", epochdate, #1/1/1970#)

    i thought *nixes used that for the start of the epoch, but M$ uses
    1/1/1980.

    Tho I may be wrong again :)

    steve

    >>>

    >>I take it that "epochdate" is the date stored in the database, and I use
    >>this function as the ControlSource for the field that I'm returning the
    >>date into?

    >
    >Yes, that is correct (with an = sign in front of it).
    >
    >Or, if epochdate is a variable, you could use that in a VBA
    >expression.
    >
    >Or, you could even use it in a calculated field in a query.
     
    Steve Holdoway, Feb 3, 2004
    #6
  7. In article <>, Steve Holdoway <> wrote:
    >On Tue, 03 Feb 2004 00:00:22 -0600, Marshall Barton
    ><> wrote:

    *SNIP*
    >>>> DateAdd("s", epochdate, #1/1/1970#)

    >i thought *nixes used that for the start of the epoch, but M$ uses
    >1/1/1980.
    >

    *SNIP*

    I thought MS used 1/1/1900, to be honest. And the time figures are
    around the 3b mark, which would be about right.
    I'll keep 1/1/1980 in mind, though, since the manufacturer of the
    software hasn't come back to me on what their epoch date is - I suspect
    I'll have to try several possibilities.

    --
    Matthew Poole Auckland, New Zealand
    "Veni, vidi, velcro...
    I came, I saw, I stuck around"

    My real e-mail is mattATp00leDOTnet
     
    Matthew Poole, Feb 3, 2004
    #7
  8. >>>Marshall Barton wrote:
    >>>>Sounds like they may be Unix type dates. Try this kind of
    >>>>expression:
    >>>>
    >>>> DateAdd("s", epochdate, #1/1/1970#)



    Steve Holdoway wrote:
    >i thought *nixes used that for the start of the epoch, but M$ uses
    >1/1/1980.


    Dates in ***Access*** are Days since 30 Dec 1899.

    The base date is easy to determine within whatever system
    you're using, just format 0 as a date.
    --
    Marsh
    MVP [MS Access]



    >>Matthew Poole wrote:
    >>>I take it that "epochdate" is the date stored in the database, and I use
    >>>this function as the ControlSource for the field that I'm returning the
    >>>date into?

    >>

    >Marshall Barton wrote:
    >>Yes, that is correct (with an = sign in front of it).
    >>
    >>Or, if epochdate is a variable, you could use that in a VBA
    >>expression.
    >>
    >>Or, you could even use it in a calculated field in a query.
     
    Marshall Barton, Feb 3, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael
    Replies:
    1
    Views:
    4,230
    Doug McIntyre
    Jan 14, 2005
  2. Jim Kroger
    Replies:
    2
    Views:
    1,116
    Brian H¹©
    Oct 14, 2003
  3. pbdb

    Date/Time

    pbdb, Jun 20, 2004, in forum: Computer Support
    Replies:
    6
    Views:
    550
    Boomer
    Jun 20, 2004
  4. nemo2

    Netgear DM602 ADSL modem and date/time

    nemo2, Jun 28, 2004, in forum: Computer Support
    Replies:
    0
    Views:
    1,166
    nemo2
    Jun 28, 2004
  5. Whothehell
    Replies:
    2
    Views:
    3,921
    Whothehell
    Jul 27, 2006
Loading...

Share This Page