How to query data in specific time like: I want to select all records stored between 1 pm and 5 pm during a month , when the time stored in datetime column in Unix timestamp format like so "1403830861".
-
Have a look at Date and time functionsuser1406062– user14060622014-06-28 11:00:03 +00:00Commented Jun 28, 2014 at 11:00
-
What are you tried so far?Sadikhasan– Sadikhasan2014-06-28 11:01:50 +00:00Commented Jun 28, 2014 at 11:01
-
@user3462064, sorry I fixed it nowuser1406062– user14060622014-06-28 11:04:16 +00:00Commented Jun 28, 2014 at 11:04
Add a comment
|
1 Answer
The FROM_UNIXTIME function can convert a unix timestamp to a date. The %k format (hour represented as an in 0..23) seems to fit the bill nicely. The month could be easily extracted in the same fashion, using the %m format and the year using %Y. E.g., the following query would return only results from November 2014:
SELECT *
FROM my_table
WHERE FROM_UNIXTIME (timestamp_column, '%k') BETWEEN 13 AND 17 AND
FROM_UNIXTIME (timestamp_column, '%m') = 11 AND
FROM_UNIXTIME (timestamp_column, '%Y') = 2014
1 Comment
Sadikhasan
You have to add one more condition for month user want all record during month