Postgres returns different results depending on client

Question

I have deployed an app to both Heroku and AWS Beanstalk. They both query the same Postgres database instance (in AWS RDS) with this SQL query as shown below to query the counts in the last 16 days including today

SELECT
    d.date AS interval,
    count(ct.id) AS count
FROM (SELECT
        to_char(date_trunc('day', (CURRENT_DATE - offs)), 'YYYY-MM-DD') AS date
      FROM generate_series(0, 15) AS offs) d
   LEFT OUTER JOIN my_table ct ON d.date = to_char(date_trunc('day', ct.created_at::timestamp WITH time zone at time zone 'US/Eastern'),    'YYYY-MM-DD')
WHERE
    ct.inserted_at::bigint >= 1564200000000.0
GROUP BY
    d.date;

The epoch in millisecond in the WHERE clause is programmatically derived as the midnight 15 days ago in US Eastern timezone. I have checked that this value is the same when the query was issued on AWS Beanstalk and Heroku, so it shouldn't be the factor in play here.

Heroku app gives me 16 rows as expected, the same as if I query from my local computer. However, the AWS Beantalk server gets 15 rows back without the data point for today. I feel it must be timezone related. I'm new to Postgres and got this query by Googling. Is CURRENT_DATE different on different machines hence causing the issue? SQL masters please help me out here, thanks in advance!

Answer 1

CURRENT_DATE depends on the time zone of the session. If it's not set at the session level, it defaults to the default time zone of the user, then to the default for the database, then the default for the cluster.

An example:

postgres=# set time zone 'UTC';
SET
postgres=# select current_date;
 current_date
--------------
 2019-08-12
(1 row)
postgres=# set time zone 'Australia/ACT';
SET
postgres=# select current_date;
 current_date
--------------
 2019-08-13
(1 row)

Additionally, depending on the data type of ct.created_at, this can also be affected by the session time zone. Here's an example:

-- A simple table with two columns, one w/o time zone and one with
postgres=# create table test (a timestamp, b timestamptz);
CREATE TABLE
postgres=# set timezone to 'UTC';
SET
postgres=# insert into test values (now(), now());
INSERT 0 1
postgres=# insert into test values (now(), now());
INSERT 0 1
postgres=# insert into test values (now(), now());
INSERT 0 1
postgres=# select a::timestamp with time zone at time zone 'US/Eastern', b::timestamp with time zone at time zone 'US/Eastern' from test;
          timezone          |          timezone
----------------------------+----------------------------
 2019-08-12 10:39:31.043397 | 2019-08-12 10:39:31.043397
 2019-08-12 10:39:32.57214  | 2019-08-12 10:39:32.57214
 2019-08-12 10:39:33.258001 | 2019-08-12 10:39:33.258001
(3 rows)

-- When the session time zone is set to UTC, these are the same
-- What happens when we change?

postgres=# set time zone 'US/Eastern';
SET
postgres=# select a::timestamp with time zone at time zone 'US/Eastern', b::timestamp with time zone at time zone 'US/Eastern' from test;
          timezone          |          timezone
----------------------------+----------------------------
 2019-08-12 14:39:31.043397 | 2019-08-12 10:39:31.043397
 2019-08-12 14:39:32.57214  | 2019-08-12 10:39:32.57214
 2019-08-12 14:39:33.258001 | 2019-08-12 10:39:33.258001
(3 rows)

The timestamp without time zone column is now four hours different from the other one.

So, if you are experiencing differences in your output, it's probably worthwhile to add a show time zone; statement to your script to see if there are any differences.