CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Falakienos
Falakienos

Reputation: 107

How can I select the data from the last 7 days between hours

I'm trying to query the data created 7 days ago but I need only the period of 11PM and 7AM. I'm actually I'm using SQL Server.

The table looks like that: 

COD  CREATED
---------------------------------------
6001 2017-11-22 22:03:23.5007015 -02:00
6002 2017-11-22 23:03:24.3593854 -02:00 
6003 2017-11-23 06:03:24.7344270 -02:00
6004 2017-11-23 13:03:25.1680686 -02:00
6005 2017-11-23 23:56:00.6874197 -02:00
6006 2017-11-24 05:33:58.4243013 -02:00
6007 2017-11-24 22:40:08.3255570 -02:00
6008 2017-11-24 23:42:50.9885384 -02:00

And that's what I really want to query

COD  CREATED 
---------------------------------------
6002 2017-11-22 23:03:24.3593854 -02:00
6003 2017-11-23 06:03:24.7344270 -02:00
6005 2017-11-23 23:56:00.6874197 -02:00
6006 2017-11-24 05:33:58.4243013 -02:00
6008 2017-11-24 23:42:50.9885384 -02:00

I now I can use between getdate() and getdate()-7 to get the period but I have no idea how to filter the time.

Thanks in advance for the help :)

Upvotes: 2

Views: 656

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

I can use between getdate() and getdate()-7 to get the period but I have no idea how to filter the time.

You can just use DATEPART(HOUR, datetimevalue), to get the hour.

For example, if you want only times from 11pm to 7pm:

SELECT *
  FROM theTable
 WHERE (DATEPART(HOUR, CREATED) > 22  --greater than 10pm hour
        OR                            --OR
        DATEPART(HOUR, CREATED) < 8   --less than 8pm hour
       )
    --put any other datediff logic here

Produces output:

COD     CREATED
6002    2017-11-22 23:03:24.3594 -02:00
6003    2017-11-23 06:03:24.7344 -02:00
6005    2017-11-23 23:56:00.6874 -02:00
6006    2017-11-24 05:33:58.4243 -02:00
6008    2017-11-24 23:42:50.9885 -02:00

In this case I used datetimeoffset(4) for the CREATED column to allow for the -02:00 timezone.

If you want to see the full code: http://sqlfiddle.com/#!6/963a1/7/0

Upvotes: 1

Hadi
Hadi

Reputation: 37368

If you are using SQL Server 2008 or newer version, you can cast to Time data type

SELECT * FROM TBL
WHERE (CAST(CREATED AS TIME) >= '23:00:00' OR CAST(CREATED AS TIME) <= '07:00:00')
AND CONVERT(DATE,CREATED) BETWEEN  DATEADD(DAY,-7,CONVERT(DATE,GETDATE())) AND CONVERT(DATE,GETDATE())

SQLFiddle

Else you can use the following query:

SELECT * FROM TBL
WHERE CONVERT(DATE,CREATED) BETWEEN  DATEADD(DAY,-7,CONVERT(DATE,GETDATE())) AND CONVERT(DATE,GETDATE())
AND ( DATEPART('hour',CREATED) >= 23 
      OR DATEPART('hour',CREATED) <= 6 )

Note: i used CONVERT(DATE,CREATED) to eliminate the time when comparing dates

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35623

For best performance potential avoid using functions on the data. This maximizes the opportunity to leverage from an index on the created column, and also saves on performing function calls on every row of data.

SELECT * 
FROM Table1
WHERE created >= dateadd(hour,-145,dateadd(dd, datediff(dd,0, getDate()), 0))
AND created < dateadd(hour,-137,dateadd(dd, datediff(dd,0, getDate()), 0))

You can choose which method you prefer, both sets below work by "truncating" getdate() setting the time to 00:00:00+0000000. Then deduct (-724)+23 and (-724)+23+8 (-145 and -137).

SELECT
  dateadd(hour,-145,dateadd(dd, datediff(dd,0, getDate()), 0))
, dateadd(hour,-137,dateadd(dd, datediff(dd,0, getDate()), 0))

, dateadd(hour,-145,convert(datetime,convert(date,getdate())))
, dateadd(hour,-137,convert(datetime,convert(date,getdate())))

If necessary add or subtract 24 from those numbers to find the time period you need.

Oh, and please note that "between" is NOT a good thing for date/time ranges. Do NOT use "between" here. For more on this topic read Bad habits to kick : mis-handling date / range queries

CREATE TABLE Table1
    ([COD] int, [CREATED] datetime2)
;
    
INSERT INTO Table1
    ([COD], [CREATED])
VALUES
  (6001, '2017-11-22 22:03:23.5007015 -02:00')
, (6002, '2017-11-22 23:03:24.3593854 -02:00')
, (6003, '2017-11-23 06:03:24.7344270 -02:00')
, (6004, '2017-11-23 13:03:25.1680686 -02:00')
, (6005, '2017-11-23 23:56:00.6874197 -02:00')
, (6006, '2017-11-24 05:33:58.4243013 -02:00')
, (6007, '2017-11-24 22:40:08.3255570 -02:00')
, (6008, '2017-11-24 23:42:50.9885384 -02:00')
;

update table1
set created = dateadd(day,-7,created)
;

Results:

|  COD |                     CREATED |
|------|-----------------------------|
| 6001 | 2017-11-15 22:03:23.5007015 |
| 6002 | 2017-11-15 23:03:24.3593854 |
| 6003 | 2017-11-16 06:03:24.7344270 |
| 6004 | 2017-11-16 13:03:25.1680686 |
| 6005 | 2017-11-16 23:56:00.6874197 |
| 6006 | 2017-11-17 05:33:58.4243013 |
| 6007 | 2017-11-17 22:40:08.3255570 |
| 6008 | 2017-11-17 23:42:50.9885384 |

Query

SELECT * 
FROM Table1
WHERE created >= dateadd(hour,-145,dateadd(dd, datediff(dd,0, getDate()), 0))
AND created < dateadd(hour,-137,dateadd(dd, datediff(dd,0, getDate()), 0))
;

Results

|  COD |                     CREATED |
|------|-----------------------------|
| 6005 | 2017-11-16 23:56:00.6874197 |
| 6006 | 2017-11-17 05:33:58.4243013 |

Demo

Upvotes: 2

Ali Azam
Ali Azam

Reputation: 2115

You can use below query:

SELECT *
FROM  Table1
WHERE Created >= (DATE(NOW()) - INTERVAL 7 DAY)
ORDER BY COD DESC

Upvotes: 0

Related Questions