CODEWITHSUNDEEP
mysql
stef
stef

Reputation: 27789

MySQL compare date with timestamp

I have a VARCHAR field completion_date that contains a timestamp date (ex 1319193919). Is it possible to run a query on such a field that compares it with NOW() ? I'm trying:

SELECT * FROM (`surveys`) WHERE `active` = '1' AND `archived` = '0' AND `completion_date` > 'NOW()'

But the results are not really what I'm expecting, is this cause of the VARCHAR? If so, what kind of date field am I better off using? The data must remain a Linux timestamp.

Upvotes: 0

Views: 2040

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270767

Convert NOW() to a timestamp using UNIX_TIMESTAMP()

SELECT * 
FROM (`surveys`) 
WHERE `active` = '1' AND `archived` = '0' AND `completion_date` > UNIX_TIMESTAMP(NOW())

Also, remove the quotes you had around 'NOW()'

mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1319288622 |
+-----------------------+

N.B. In case you need it, the inverse of this function is FROM_UNIXTIME() to convert a timestamp into the default MySQL DATETIME format.

As mentioned in comments below, if you have the ability to make the change, it is recommended to use a real DATETIME type instead of VARCHAR() for this data.

Upvotes: 3

tvanfosson
tvanfosson

Reputation: 532715

A Linux timestamp can easily be stored in a BIGINT (or an UNSIGNED INT), which would make the type of comparisons you're trying to do possible. A VARCHAR is going to do a lexical, not numeric, comparison and which is NOT what you want. Using a BIGINT in conjunction with converting NOW() with UNIX_TIMESTAMP() should get you what you want.

It might even be better to store it using a DATETIME data type and do the conversion when you select the data. Storing it as a DATETIME future proofs your application in the event that you move to or add a different platform where a Linux timestamp isn't appropriate. Then you only need to modify your code, not convert your data to have it continue to work. I'm working on a project now where dates were stored as character data and it's been no end of problems getting the old data into shape to use with the new application, though you might experience fewer problems than us because you're storing a time stamp, not a formatted date, with its attendant typos.

Upvotes: 0

Related Questions