in reply to MySQL Timestamp comparison

Dates/Times tend to be handled differently by different databases. I've always tried to stay database-neutral in terms of date/time handling - a technique I like to use is to convert timestamp fields into "seconds since the epoch" which is an integer number of seconds since 1/1/1970. Then doing date calculations are easy. MySQL has a function(MySQL specific) called UNIX_TIMESTAMP() which returns the date as the number of seconds since the epoch. If you use a TIMESTAMP or DATETIME column, you can write a subroutine to convert that into an "seconds since the epoch" number.

Replies are listed 'Best First'.
Re: Re: MySQL Timestamp comparison
by erasei (Pilgrim) on Oct 30, 2003 at 15:48 UTC
    If you use a TIMESTAMP or DATETIME column, you can write a subroutine to convert that into an "seconds since the epoch" number.

    This isn't necessary. The UNIX_TIMESTAMP MySQL function takes a datetime (any date type column) as an argument and returns the epoch of the time passed; such as: SELECT UNIX_TIMESTAMP(myDateCol) FROM TABLE;

    Also, MySQL has another function called INTERVAL that allows you to do math on the date in the database itself, such as: select * from table where myDateCol < current_date - interval 5 day; will show you everything older than five days ago.

    When doing database programming, the hardest thing for me to get over was to try and do everything in the program (in Perl, in C, etc) when the database is (in _most_ cases) going to be able to do it for you a lot more efficiently and a lot faster. Let the database do it's job.. it likes it, really.

[reply]
      The point in creating a subroutine to take in a date/time and return an "seconds since the epoch", is not so much for MySQL specifically, but more for portability - other databases don't have a UNIX_TIMESTAMP() function, and therefore can't give back a date/time column in "seconds since the epoch". So your subroutine can function in a date/time portability role - to convert a date/time, say coming in in YYYY-MM-DD HH:MM:SS format, into a number that is "seconds since the epoch".
[reply]
        Very true, but in you have already included a MySQL specific function (UNIX_TIMESTAMP) in your above example thus excluding you from any platform independence. Having one function in your script that requires MySQL forces all the rest of your script to require it as well by default (unless you do some sort of modularized set up, but a simple script won't cope.) Therefore your argument of having the subroutine to do the conversion to be cross platform is moot because you are already requiring MySQL.
[reply]

In Section Seekers of Perl Wisdom