Project

General

Profile

Actions

Bug #16349

closed

[API] Timestamp comparisons are incorrect when database session uses a non-UTC time zone

Added by Tom Clegg over 4 years ago. Updated over 4 years ago.

Status:
Resolved
Priority:
Normal
Assigned To:
Category:
API
Target version:
Story points:
-
Release relationship:
Auto

Description

As an example, when we update the expiry time to Time.now + 5.minutes (as we do when caching a token from a remote cluster), depending on time zone configuration the expiry time that gets stored in the database might already be in the past, i.e., fail the expires_at > current_timestamp test.

This may explain why, given that we use "timestamp without time zone" columns:

create temporary table foo (
  twith timestamp with time zone,
  twithout timestamp without time zone
);

insert into foo values (current_timestamp, current_timestamp);

select * from foo;
             twith             |          twithout          
-------------------------------+----------------------------
 2020-04-22 12:40:20.796205-04 | 2020-04-22 12:40:20.796205

select extract(epoch from twith) - extract(epoch from twithout) from foo;
 ?column? 
----------
    14400

select '2020-04-22 15:36:15 UTC' - twith, '2020-04-22 15:36:15 UTC' - twithout from foo;
     ?column?     |    ?column?     
------------------+-----------------
 -00:01:45.153064 | 03:58:14.846936

set time zone 'UTC';
SET

select * from foo;
             twith             |          twithout          
-------------------------------+----------------------------
 2020-04-22 16:40:20.796205+00 | 2020-04-22 12:40:20.796205


Subtasks 1 (0 open1 closed)

Task #16358: Review 16349-non-utc-timestampsResolvedTom Clegg04/27/2020Actions
Actions

Also available in: Atom PDF