CODEWITHSUNDEEP
sqlamazon-redshift
AndyP
AndyP

Reputation: 607

How to use timestamp column to get data between two dates?

I have a below query which gives me data for past X days. Instead of that I wanted to get data in between two dates which should be configurable if possible? Is there any way to do it?

SELECT processId,
 houroftheday,
 minuteofhour,
 listagg(clientId, ',') within group (order by minuteofhour) as clientIds,
 count(*) as psg
 FROM data.process
 where kite = 'BULLS'
 and code is null
 and timestampinepochsecond > date_part(epoch, sysdate) - X*24*60*60
 group by 1, 2, 3

timestampinepochsecond column has this value - 1599608655

How can I use timestampinepochsecond column to get data in between two dates? Something like below:

 timestampinepochsecond between 2020-12-01 AND 2021-01-05

Update

I am trying to declare two variables and use those in my query as shown below but it doesn't work-

CREATE TEMP TABLE tmp_variables AS SELECT 
   cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint) AS StartDate, 
   cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-10 16:22:17.897' ) as bigint)      AS EndDate,
   5556::BIGINT       AS some_id;

SELECT StartDate, EndDate FROM tmp_variables;

Upvotes: 0

Views: 338

Answers (1)

Dawood Awan
Dawood Awan

Reputation: 7348

I think you will have to convert date time to Epoch Timestamp and then query based on that:

    -- this will get the the timestamp in seconds
    DECLARE @startTimeStamp bigint = cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint)

 DECLARE @endTimeStamp bigint = cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-10 16:22:17.897' ) as bigint)
    

-- then your query.

SELECT processId,
 houroftheday,
 minuteofhour,
 listagg(clientId, ',') within group (order by minuteofhour) as clientIds,
 count(*) as psg
 FROM data.process
 where kite = 'BULLS'
 and code is null
 and timestampinepochsecond >= @startTimeStamp  AND  timestampinepochsecond 
 < @endTimeStamp
 group by 1, 2, 3

if this is a stored procedure - you could pass in start date and end date is an parameters - then convert to Epoch Seconds.

Upvotes: 1

Related Questions