How to extract hour for MySQL FROM_UNIXTIME () results?

Question

I have a column with measurement times in timestamps in INT, and I need group data at intervals of an hour. I have already discovered the MySQL function FROM_UNIXTIME (), but is there a built-in tool in MySQL to extract hours from the results of this function?

Answer 1

I think you mean FROM_UNIXTIME

 GROUP BY DATE_FORMAT(FROM_UNIXTIME(time_column), '%Y %m %d %H')) 

Answer 2

 SELECT * FROM table GROUP BY DATE_FORMAT(FROM_UNIXTIME(time_col_name), '%H') 

Check out the MySQL documentation for the date .

Edit:

Use DATE_FORMAT instead to reliably determine the format of the hour returned. See the DATE_FORMAT Documentation . Note : the format% H will return the hour value in the format 00, 01, ... 23.

Answer 3

Sort of:

 SELECT HOUR(FROM_UNIXTIME(unixCol)) FROM tbl GROUP BY HOUR(FROM_UNIXTIME(unixCol)) 

HOUR will select the hour after the UNIX timestamp is closed before the date.

See documentation

Answer 4

Try putting the database data in a variable as a date, and then repeat it.

 $timestamp = strtotime($db_data); echo date("mdY", $timestamp); 

Answer 5

I know that a few years after the initial question, but if you are dealing with larger datasets, this may take longer due to the call of two functions for each row in the query set.

I managed to shave a couple of seconds off my time using a bit of modular arithmetic

 GROUP BY FLOOR(time_col/(60*60) MOD 24) 

Hope this helps :)