Menu
I have a column named updatetime that is a timestamp. So, for example, an average looking value could be: 2011-02-01 09:00:51. I want to be able to search through and return all results for a particular hour of the day regardless of the date.
For example if I searched the column for values BETWEEN 09:00:00 AND 09:59:99 it would return:
2011-02-01 09:00:51
2011-01-31 09:20:51
2011-01-11 09:55:44
etc....
SELECT * FROM table WHERE updatetime ......
Thoughts?
673
To get a day of week from a timestamp, use the DAYOFWEEK() function: -- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday SELECT dayofweek('2018-12-12'); -- returns the string day name like Monday, Tuesday, etc SELECT dayname(now()); To convert a timestamp to a unix timestamp (integer seconds):
If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';
MySQL HOUR() function MySQL HOUR() returns the HOUR of a time. The return value is within the range of 0 to 23 for time-of-day values. The range of time values may be larger than 23. Where time is a time.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
You could use the HOUR() function:
SELECT * FROM 'table' WHERE HOUR(`updatetime`) = 9
Alas, this query's performance will be horrible, as soon as you go over a few thousand rows - functions aren't indexable, so there will be a full table scan each time this query runs.
What we did in a similar situation: we created another column updatetime_hour, indexed it, and populated it on insert (and updated on update); then the query becomes fast:
SELECT * FROM 'table' WHERE `updatetime_hour` = 9
Yes, we have denormalized the data, and it's a bit more housekeeping, but I have yet to see a faster solution. (We considered and measured insert and update triggers to populate the updatetime_hour from updatetime, but decided against for performance; see if they would be useful for you.)
166
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
