Tutorial :How to validate expiry date on MySQL query



Question:

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?


Solution:1

SELECT * FROM download WHERE expiry_date > CURRENT_TIMESTAMP  

or

SELECT * FROM download WHERE expiry_date > NOW()  


Solution:2

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();  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »