Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Time with Epoch earlier than 1970 question

Reply
Thread Tools

Time with Epoch earlier than 1970 question

 
 
Tyler Cruz
Guest
Posts: n/a
 
      02-08-2004
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


 
Reply With Quote
 
 
 
 
Gunnar Hjalmarsson
Guest
Posts: n/a
 
      02-08-2004
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

 
Reply With Quote
 
 
 
 
ko
Guest
Posts: n/a
 
      02-08-2004
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...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
 
Reply With Quote
 
Sam Holden
Guest
Posts: n/a
 
      02-08-2004
On Sun, 08 Feb 2004 05:35:29 GMT, Tyler Cruz <(E-Mail Removed)> 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
 
Reply With Quote
 
Gregory Toomey
Guest
Posts: n/a
 
      02-08-2004
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unix time (predating epoch of Jan-1970) Grey Alien C Programming 5 07-20-2007 12:21 AM
Need to use dates earlier than 1900 (Time library says out of range for dates < 1900) me@benjaminarai.com Ruby 1 07-17-2007 02:25 PM
covert time from date Hour min sec format to epoch time i.e time since 1 jan 1970 in C Summu82 C Programming 5 06-07-2006 02:51 PM
dates earlier than 1970? Kevin Howe Ruby 2 11-03-2004 07:47 PM
Converting epoch time to "date/time" Marshall Barton NZ Computing 7 02-03-2004 04:29 PM



Advertisments