Page 1 of 1

PHP/MySQL Date Question

Posted: Mon Jul 21, 2008 5:34 pm
by aliasxneo
Not sure which forum this belongs in since the issue relates both to PHP and MySQL.

I have a table in MySQL which uses the MySQL 'datetime' to hold dates. What I need to do is basically compare those dates to a Unix timestamp and determine if they are after the Unix timestamp I'm comparing too.

The problem is I'm not sure how to do this. Is there MySQL functions to convert Unix timestamps to datetimes from which I can then compare the two? Or do I need to somehow convert the Unix timestamp to that of a 'datetime' and then try to compare it like that?

Cheers,
Josh

Re: PHP/MySQL Date Question

Posted: Mon Jul 21, 2008 6:36 pm
by alex.barylski
Don't use MySQL DATETIME type...just use an integer and store the native timestamp makes comparison easier, faster and more portable.

Re: PHP/MySQL Date Question

Posted: Mon Jul 21, 2008 8:13 pm
by manixrock
you can use a mysql DATETIME field and the mysql UNIX_TIMESTAMP(date) function to get the unix timestamp.

More info here: http://dev.mysql.com/doc/refman/5.0/en/ ... -timestamp

Re: PHP/MySQL Date Question

Posted: Tue Jul 22, 2008 12:13 am
by gmapsuser
In MySql You can convert from DATETIME field to Unix Timestamp format using UNIX_TIMESTAMP() function or vice versa using FROM_UNIXTIME() function.

You can go through the following links for further references.

http://dev.mysql.com/doc/refman/5.0/en/ ... m-unixtime
http://dev.mysql.com/doc/refman/5.0/en/ ... -timestamp

Re: PHP/MySQL Date Question

Posted: Tue Jul 22, 2008 12:20 am
by Zoxive
Hockey wrote:Don't use MySQL DATETIME type...just use an integer and store the native timestamp makes comparison easier, faster and more portable.
If you do that, then you will not be able to use the mysql date functions for the other queries using that table.