CODEWITHSUNDEEP
mysqldatetimeintervals
Joooooom
Joooooom

Reputation: 113

SQL select all but exlude results when datetime is between midnight and 6am

I have a basic problem with an SQL query and I can't find the solution. I have a table where the first column is a epoch timestamp and a new entry is saved every 15min, and I want to select all the entires between two dates. Let's say a month. That's easy, I just use a regular query like

SELECT * from MyTable where date > UNIX_TIMESTAMP("2015-04-01 00:00:00") and date < UNIX_TIMESTAMP("2015-05-01 00:00:00"); .

But now let's say I want to select all my entries for the whole month just like before, but EXCLUDING the entries that have been added between midnight and 6am. How could I do that ? I can't imagine any simple and straight forward way to do it. My query is executed in a Perl script so I could do a loop that would query all the results for anything above 6am every day but it would be "ugly".

Thanks !

(Yes i know I named my column date and date is actually a function, it's bad i won't dit it again)

Upvotes: 0

Views: 813

Answers (3)

Joooooom
Joooooom

Reputation: 113

A big thank you for those you responded, I used your exemples and managed to make em work with my table and my data. Here's the final request :

SELECT * from My Table
where time(FROM_UNIXTIME(`date`)) > '06:00:00'
and `date` > UNIX_TIMESTAMP("2015-05-01 00:00:00") 
and `date` < UNIX_TIMESTAMP("2015-05-02 00:00:00");

Basically the first answer was correct with only the fact that "where time..." needed the function FROM_UNIXTIME to work too.

Thanks again :)

Upvotes: 1

jpw
jpw

Reputation: 44891

You can use the time() function to extract the time part of the datetime column and then check that the time is after 6 am.

SELECT * from MyTable 
where time(`date`) > '06:00:00'
and `date` > UNIX_TIMESTAMP("2015-04-01 00:00:00") 
and `date` < UNIX_TIMESTAMP("2015-05-01 00:00:00")

Sample SQL Fiddle

Upvotes: 2

Kritner
Kritner

Reputation: 13765

what about something like:

SELECT * 
from MyTable 
where mydate > UNIX_TIMESTAMP("2015-04-01 00:00:00") 
    and date < UNIX_TIMESTAMP("2015-05-01 00:00:00")
    and HOUR(time(mydate)) >= 6;

Upvotes: 1

Related Questions