Declare variables in redshift and use it in the select query

Question

I am trying to declare variables and use it in my sql query as shown below but it gives me error:

-- 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)

-- my 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

I read it here as it isn't possible so how can I rewrite my above query so that it can work fine? I tried their example but having issues in converting it out.

Below is what I have tried but I get syntax error:

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;

Here is my demo where it gives the error:

Here is my [demo](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a1755200dcbe2dc5c418b8a6314b0286) — AndyP, Jan 08 '21 at 01:27

score 1 · Answer 1 · answered Jan 08 '21 at 06:37

1

So Redshift is not exactly Postgres and Postgres does not have DATEDIFF so your dbfiddle example doesn't help. I ran the code you provided on Redshift and got the following error message:

[Amazon](500310) Invalid operation: function pg_catalog.date_diff(character varying, character varying, character varying) does not exist;

This basically lays out the issue '1970-01-01 00:00:00.000' is not a time stamp, it's a text string. This needs to be cast to timestamp for use by DATEDIFF.

DATEDIFF returns a bigint so casting to bigint is not needed.

Also, there is already a builtin function to get the seconds since epoch (1/1/1970) - DATEPART(). Like this:

date_part(epoch, '2016-12-09 16:22:17.897'::timestamp)

Now DATEPART returns a DOUBLE data type so you may want to cast this it BIGINT if you don't want the sub-second information.

If you like your style it will work like this:

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

answered Jan 08 '21 at 06:37

Bill Weiner

8,835
2
7
18

thanks for your suggestion. I tried but it doesn't work still. I copied your suggestion exactly on my demo and same issue. Here is new [demo](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=05e2f8227ec1ee9ed9b76628b2335e80) – AndyP Jan 08 '21 at 06:53
ohh sorry so you mean to say it won't run on the demo since it uses postgresql hmmm – AndyP Jan 08 '21 at 06:54
Yes. Redshift split from Postgres on V8.0 (long ago). Even then then there were significant differences in the supported SQL because of Redshift's clustered columnar architecture. Since then Redshift has added features / functions from other databases such as datediff(). If you need your SQL to work in both Redshift and a Postgres emulator you need to use only common functions such as EXTRACT() which can be used to compute the same result - extract(epoch, '2016-12-09 16:22:17.897'::timestamp) - see: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=67381980ef5bb5433c6af35368df9fe7 – Bill Weiner Jan 08 '21 at 17:03
yeah on the demo it looks good but for some reason when I try on my internal company app which uses redshift behind the scene - it is giving me an error as - `SYNTAX_ERROR :: Invalid operation: relation "tmp_variables" does not exist;`. Any thoughts why it could be? I tried exact same query as you have in your demo. – AndyP Jan 08 '21 at 21:27
Also is there any way to put two dates in my sql query directly instead of storing it in temp table and then using it? Like - `and timestampinepochsecond >= startDate AND timestampinepochsecond < endDate`. I am just exploring other option if this temp table thing doesn't work for us internally. – AndyP Jan 08 '21 at 21:29
Best guess not knowing what your company app is: The app is submitting the 2 queries in different transactions so the temp table does not exist in for the second command. You could move to a CTE (WITH clause) to make these one statement. – Bill Weiner Jan 09 '21 at 00:38
As to any different ways to do this - most benches and apps have some ability to define variables and use these in queries before the queries go to the database. To be clear the substitution is being done before the query goes to Redshift. You could use this capability to set these constants. However, the syntax for such replacement strings (variables) is highly bench / app dependent so the solution will be tied to the tool you are using at that time. – Bill Weiner Jan 09 '21 at 00:42
If you would like to go the CTE route I updated the fiddle at https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=4b421106f542a9335dc5ab1d869eb7ae The downside with the CTE approach is that these constants are local to the query. Where the temp table approach is usable by any query in the session – Bill Weiner Jan 09 '21 at 00:45

Declare variables in redshift and use it in the select query

1 Answers1