CODEWITHSUNDEEP
phpmysqldatetime
kampit
kampit

Reputation: 149

How to validate expiry date on MySQL query

Here is my current mysql table

id  | file        |  expiry_date
---------------------------------------
1   | sample.zip  |  2010-02-03 11:07:03
2   | sample2.zip |  2010-07-13 11:07:03

Query:

SELECT * 
  FROM download 
 WHERE expiry_date` `how to validate here`

I want to validate if expiry_date is expired the file cannot download.

How to do that?

Upvotes: 12

Views: 20699

Answers (2)

John Conde
John Conde

Reputation: 219934

SELECT * FROM download WHERE expiry_date > CURRENT_TIMESTAMP

or

SELECT * FROM download WHERE expiry_date > NOW()

Upvotes: 19

David L Ernstrom
David L Ernstrom

Reputation: 459

One concern you need to consider is what timezone your expiry_date is stored in vs. the timezone of your mysql server. I have used solutions like the following:

SELECT * FROM `download` WHERE `expiry_date` > NOW();

The solution, however, did not necessarily give me the correct answer I was looking for as the NOW() function is localized to the timezone of the mysql server. Unless your expiry_dates went into the server already localized to your server with NOW(), you'll get an incorrect comparison.

In all of our systems, we store timestamps in the database using UTC. Unfortunately, the data center we host with requires the servers to be localized to EST, which potentially messes up all of our comparisons. The solution was to use the UTC_TIMESTAMP() function, which returns the UTC date and time un-localized.

SELECT * FROM `download` WHERE `expiry_date` > UTC_TIMESTAMP();

Upvotes: 3

Related Questions