Menu
How do I query a mysql db to return all records with a datetime older than 1 week ago. Note that the datetime table stores everything in UTC, and I should be comparing it in that itself.
Just to be clear - I'm looking for a pure mysql query.
802
MySQL - UTC_DATE() Function The MYSQL UTC_DATE() is used to get the current UTC date. The resultant value is a string or a numerical value based on the context and, the date returned will be in the 'YYYY-MM-DD' or YYYYMMDD format.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
SELECT col1, col2, col3 FROM table WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();
To get dates older than 1 week, you can use the following syntax −. select *from yourTableName where yourColumnName < now () - interval 1 week; To understand the above concept, let us create a table. The query to create a table is as follows −.
In this tutorial, we will study the MySQL UTC_TIME (), UTC_DATE () and UTC_TIMESTAMP () functions. Coordinated Universal Time or UTC is the primary time standard by which the world regulates clocks and time. It is within about 1 second of mean solar time at 0° longitude, and is not adjusted for daylight saving time.
How to Query Date and Time in MySQL MySQL has the following functions to get the current date and time: SELECT now(); -- date and time SELECT curdate(); --date SELECT curtime(); --time in 24-hour format
1 Definition and Usage. The WEEK () function returns the week number for a given date (a number from 0 to 53). 2 Syntax 3 Parameter Values. Specifies what day the week starts on. 4 Technical Details. From MySQL 4.0 5 More Examples
SELECT * FROM tbl WHERE datetime < NOW() - INTERVAL 1 WEEK If your table stores datetimes in different timezone than what NOW() returns, you can use UTC_TIMESTAMP() instead to get the timestamp in UTC.
114
SELECT * FROM table WHERE DATEDIFF(NOW(),colname) > 7; 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
