Time with Epoch earlier than 1970 question

Discussion in 'Perl Misc' started by Tyler Cruz, Feb 8, 2004.

  1. Tyler Cruz

    Tyler Cruz Guest

    Hi,

    I have a database where a date is entered through a form via a user in the
    format (YYYY-MM-DD) format and then converted to epoch, and stored in a
    MySQL database. The reverse is done when extracting this information from
    the database and posting it to the web.

    However, since epoch is measured from January 1, 1970, I cannot enter dates
    earlier than that period. I had wanted to store the dates in epoch as it
    makes for easy parsing with the POSIX module, as well as allowing MySQL to
    sort by date through epoch. This is why I didn't want to simply return a
    string as a date; sorting would be out of the question.

    Could somebody please help me?

    Thanks,

    Tyler Cruz
    Tyler Cruz, Feb 8, 2004
    #1
    1. Advertising

  2. Tyler Cruz wrote:
    > This is why I didn't want to simply return a string as a date;
    > sorting would be out of the question.


    Why would sorting be out of the question if the format is YYYY-MM-DD?

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
    Gunnar Hjalmarsson, Feb 8, 2004
    #2
    1. Advertising

  3. Tyler Cruz

    ko Guest

    Tyler Cruz wrote:
    > Hi,
    >
    > I have a database where a date is entered through a form via a user in the
    > format (YYYY-MM-DD) format and then converted to epoch, and stored in a
    > MySQL database. The reverse is done when extracting this information from
    > the database and posting it to the web.
    >
    > However, since epoch is measured from January 1, 1970, I cannot enter dates
    > earlier than that period. I had wanted to store the dates in epoch as it
    > makes for easy parsing with the POSIX module, as well as allowing MySQL to
    > sort by date through epoch. This is why I didn't want to simply return a
    > string as a date; sorting would be out of the question.
    >
    > Could somebody please help me?
    >
    > Thanks,
    >
    > Tyler Cruz


    The following links have nothing to do with Perl, but they should help:

    http://www.mysql.com/doc/en/Date_and_time_types.html
    http://www.mysql.com/doc/en/Date_and_time_functions.html
    http://www.mysql.com/doc/en/Date_calculations.html
    http://www.mysql.com/doc/en/MySQL_indexes.html

    If you use the MySQL functions you won't need to do any conversion on
    the data as specified, and if you put an index on the date column in
    question you don't have to worry about sorting either.

    HTH - keith
    ko, Feb 8, 2004
    #3
  4. Tyler Cruz

    Sam Holden Guest

    On Sun, 08 Feb 2004 05:35:29 GMT, Tyler Cruz <> wrote:
    > Hi,
    >
    > I have a database where a date is entered through a form via a user in the
    > format (YYYY-MM-DD) format and then converted to epoch, and stored in a
    > MySQL database. The reverse is done when extracting this information from
    > the database and posting it to the web.
    >
    > However, since epoch is measured from January 1, 1970, I cannot enter dates
    > earlier than that period. I had wanted to store the dates in epoch as it
    > makes for easy parsing with the POSIX module, as well as allowing MySQL to
    > sort by date through epoch. This is why I didn't want to simply return a
    > string as a date; sorting would be out of the question.
    >
    > Could somebody please help me?


    Mysql has a number of date types which can handle earlier dates, and all
    of them can be sorted just fine by mysql.

    In fact the date type is exactly the format you use for I/O and supports
    dates ranging from years 1000-9999, so why bother with converting to and
    from an unsuitable epoch format (the 'date' type I mention is in fact an
    epoch format, but it starts at 1000-01-01).

    A simple roll your own day+month*32+year*(31+12*32+1) will give you over
    10 million years of dates. If you prefer bitshifting to multiplies and
    modulus operations then allocating 5 bits for the day, 4 bits for the
    year, and the remaining 23 bits for the year gives over 8 million years
    of dates. Since there are always 12 months in a year, you could also use
    day+yearmonth*32 and say year=yearmonth/12, month=yearmonth%12 giving
    over 11 million years of dates. (Note I haven't checked any of my logic
    or math, so I may have introduced an unworkable system which maps two
    different dates to the same number - it's easy enough to check, but I
    don't care enough to do so).

    Of course the mysql is implemented in C and hence it's date
    conversion functions will almost certainly be faster than a roll your
    own done in perl.

    But really, this isn't a perl issue.

    --
    Sam Holden
    Sam Holden, Feb 8, 2004
    #4
  5. Tyler Cruz wrote:

    > Hi,
    >
    > I have a database where a date is entered through a form via a user in the
    > format (YYYY-MM-DD) format and then converted to epoch, and stored in a
    > MySQL database. The reverse is done when extracting this information from
    > the database and posting it to the web.
    >
    > However, since epoch is measured from January 1, 1970, I cannot enter
    > dates earlier than that period. I had wanted to store the dates in epoch
    > as it makes for easy parsing with the POSIX module, as well as allowing
    > MySQL to sort by date through epoch. This is why I didn't want to simply
    > return a string as a date; sorting would be out of the question.
    >
    > Could somebody please help me?
    >
    > Thanks,
    >
    > Tyler Cruz



    Mysql does not use epoch dates for storage - it uses at least four digit
    year. The "ORDER BY" statement uses 4 digit years.

    Mysql also runs on systems that know nothing about epochs ie WIndows.

    gtoomey
    Gregory Toomey, Feb 8, 2004
    #5
    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. Summu82
    Replies:
    5
    Views:
    861
    Richard Heathfield
    Jun 7, 2006
  2. Grey Alien

    Unix time (predating epoch of Jan-1970)

    Grey Alien, Jul 19, 2007, in forum: C Programming
    Replies:
    5
    Views:
    577
    Keith Thompson
    Jul 20, 2007
  3. Kevin Howe

    dates earlier than 1970?

    Kevin Howe, Nov 3, 2004, in forum: Ruby
    Replies:
    2
    Views:
    124
    Yukihiro Matsumoto
    Nov 3, 2004
  4. Replies:
    1
    Views:
    205
    Jano Svitok
    Jul 17, 2007
  5. Replies:
    3
    Views:
    176
    Dr J R Stockton
    May 21, 2007
Loading...

Share This Page