Menu
The timestamp in my database is in the following format:
2011-02-26T13:00:00-05:00
But getting the current timestamp via MySQL with:
SELECT current_timestamp();
Gives me something formatted like...
2011-02-26 13:05:00
My end goal is to go through all entries (each 2 days) and delete those older than 2 days so how would I compare the entries with the 2011-02-26T13:00:00-05:00 timestamp to the current timestamp?
777
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR . To get the difference in seconds as we have done here, choose SECOND .
Can you compare timestamps in SQL? A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type.
When comparing two TIMESTAMP WITH TIME ZONE values, the comparison is made using the UTC representations of the values. Two TIMESTAMP WITH TIME ZONE values are considered equal if they represent the same instance in UTC, regardless of the time zone offsets that are stored in the values. For example, '1999-04-15-08.00.
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days.
Try:
SELECT DATE(`yourtimestamp`) FROM thetablename WHERE DATE(`yourtimestam`) < CURDATE() - INTERVAL 2 DAY;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
