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

Question

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;

WHERE t.timestamp_col >= '2020-12-01' AND t.timestamp_col < '2021-01-05' — Meghshyam Sonar, Jan 05 '21 at 21:58
My `timestampinepochsecond` column has value like this - `1599608655` @MeghshyamSonar — AndyP, Jan 05 '21 at 22:19

score 1 · Answer 1 · answered Jan 05 '21 at 22:24

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.

Sorry I was away for a while. Looks like I cannot use `Declare variables` here and use then in select query. I checked [here](https://stackoverflow.com/questions/30822176/declare-a-variable-in-redshift/40728524) as well. Is there any way I can do this in redshift? I would like to declare `startTimeStamp` and `endTimeStamp` at the top just like you have and use them in my select query below. Is this possible to do by any chance? — AndyP, Jan 07 '21 at 21:37
I updated in my question with what I have tried but it gives syntax error as obvious - Any thoughts what is wrong I am doing there? — AndyP, Jan 07 '21 at 22:52

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

1 Answers1