SQL query SELECT time intervals

Question

I'm trying to SELECT all the rows from a SQL database which are between an hour interval, for every day. The datetime column is called "Dt" and has the following datetime format: 2019-10-17 16:03:43

I'd like to extract all the rows from this table where the Dt was between 22:00:00 and 02:00:00, for everyday.

SELECT *
FROM MY_TABLE
WHERE "Dt" BETWEEN '*-*- 22:00:00' AND '*-*- 02:00:00';

where * should be any...

Thanks for your support!

EDIT: I forgot to mention: I'm using the integrated SQL interpreter from DB Browser for SQLite

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product and functions dealing with timestamp (date/time) are highly vendor specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Oct 17 '19 at 07:10

score 0 · Answer 1 · answered Oct 17 '19 at 07:09

You need to extract the time part of the date and compare that it is within the range. Since midnight is between 22 and 2, you will need to split it to two comparisons, time between 22 and 0 and between 0 and 2.

To see how to extract the time take a look at this question.

score 0 · Answer 2 · answered Oct 17 '19 at 07:15

0

With Postgres, assuming dt is defined as timestamp you can do the following:

SELECT *
FROM MY_TABLE
WHERE "Dt" BETWEEN "Dt"::date + time '22:00:00' and ("Dt"::date + 1) + time '02:00:00'

Or if you want to exclude timestamps at precisely 02:00:00

SELECT *
FROM MY_TABLE
WHERE "Dt" >= "Dt"::date + time '22:00:00' 
  and "Dt" < ("Dt"::date + 1) + time '02:00:00'

answered Oct 17 '19 at 07:15

Thank you for your help. Your propsal returns the following: Result: unrecognized token: ":" At line 1: SELECT * FROM MY_TABLE WHERE "Dt" >= "Dt" – arnaud405 Oct 21 '19 at 06:11

shubham · Answer 3 · 2019-10-17T08:00:54.453

0

 select DT_time from (
    select cast (substr(to_char(Dt,'dd-mm-yyyy HH:MM:SS'),12,2) as integer ) as DT_time from MY_TABLE )
 where  DT_time between 2 and 22;

edited Oct 17 '19 at 08:00

answered Oct 17 '19 at 07:53

shubham

99
9

Result: no such function: to_char – arnaud405 Oct 21 '19 at 06:12

score 0 · Answer 4 · answered Oct 21 '19 at 07:42

0

between 22:00:00 and 02:00:00

means:

SELECT *
FROM MY_TABLE
WHERE 
  substr(Dt, 12) BETWEEN '22:00:00' AND '23:59:59'
  OR
  substr(Dt, 12) BETWEEN '00:00:00' AND '02:00:00'

answered Oct 21 '19 at 07:42

forpas

160,666
10
38
76

Xomo · Answer 5 · 2019-10-25T12:00:19.930

-1

This will work ::

SELECT *
FROM MY_TABLE
WHERE DATEPART(HOUR, Dt)>22  
AND DATEPART(HOUR, Dt)<2

Update :

SELECT * 
FROM MY_TABLE 
WHERE Dt Between DATEADD (hour,22,DATEADD(day, DATEDIFF(day, 0, Dt), 0)) AND DATEADD (hour,2,DATEADD(day, DATEDIFF(day, -1, Dt), 0))


SELECT *
FROM MY_TABLE
WHERE DATEPART(HOUR, Dt)>22  
OR DATEPART(HOUR, Dt)<2

Above query work for you.. 1st one will check only for particular date and consecutive next date along with your time range. But If you don't care about dates and only looking for time interval in particular hours then 2nd one is for you.

For SQLite :

SELECT *
FROM MY_TABLE
WHERE strftime('%H','Dt')>22  
OR strftime('%H','Dt')<2

edited Oct 25 '19 at 12:00

answered Oct 17 '19 at 07:11

Xomo

21
6

The hour can't be greater than 22 and smaller than 2 at the same time. This query will return nothing – Oct 17 '19 at 07:18
I have just tested sir.. It's working @a_horse_with_no_name https://imgur.com/a/vYUgHEN – Xomo Oct 17 '19 at 07:27
Well `> 10 AND < 16` is something completely different than `> 22 AND < 2` try the query from your answer, not something different – Oct 17 '19 at 07:28
I am testing with current time.. If I put 22 AND 2 then it will definitely give 0 results.. cause current time is 12 over here. – Xomo Oct 17 '19 at 07:30
1

The point is: a single number can not be larger than 22 **and** smaller than 2 at the same time and that's what your answer suggests. Your condition is equivalent to `where 1=2`, see an example here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=fdf2aa5eb9397a535b52570348ebc75c – Oct 17 '19 at 07:30
Yeah I am gettting your point. Will find another solution. Thanks for the knowlege. – Xomo Oct 17 '19 at 07:32
In SQLite there are no DATEPART, DATEADD, DATEDIFF functions. – forpas Oct 25 '19 at 11:53

SQL query SELECT time intervals

5 Answers5