Yesterday - in Redshift & PostgreSQL - date addition compatibility

Question

Attempting to write queries that will be compatible with both PostgreSQL and Amazon Redshift.

Reason: Syncing data from PG to RS to perform complex queries, but in dev/QA environments budget (and DB size) dictates to stay with PG only.

Request: return yesterday's date

In PostgreSQL:

SELECT DATE((NOW() - '1 DAY'::INTERVAL));

In Redshift:

SELECT DATE(DATEADD(DAY, -1, GETDATE()));

Problem: Neither works in the other platform.

Is there a compatible way to achieve requested action? ORM is an option we'd like to avoid.

Testing on one platform and deploying another platform is a dangerous approach. At the very least, once the PostgreSQL tests pass, you should be running at least the same tests against RedShift too. — MatBailie

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2018-06-20T07:46:08

The following works in Postgres and Redshift:

ANSI standard SQL:

SELECT current_date - interval '1' day;
-- 2018-06-19 00:00:00
SELECT current_timestamp - interval '1' day;
-- 2018-06-19 13:40:06.509337+00

Postgres (and I believe Redshift as well) also supports the alternative (non-standard) interval syntax: interval '1 day'

Or more compact (not 100% ANSI SQL but works in both):

SELECT current_date - 1;
-- 2018-06-19 00:00:00
SELECT current_timestamp - 1;
-- 2018-06-19 13:40:06.509337+00