Time with Epoch earlier than 1970 question

[Tyler Cruz]

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

 

[Gunnar Hjalmarsson]

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?

 

[ko]

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

 

[Sam Holden]

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.

 

[Gregory Toomey]

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