Inserting DateTime as a String into PostgreSQL

[nashby]

Hey! I have following example app:

require 'pg'
require 'sequel'
require 'tzinfo'

Sequel.extension :named_timezones
Sequel.database_timezone = :utc
Sequel.application_timezone = 'Europe/Oslo'

DB = Sequel.connect('postgres://postgres:postgres@127.0.0.1:5434/sequel_test')

DB.create_table :items do
  primary_key :id
  DateTime :date
end

items = DB[:items]

items.insert(date: '2015-12-04T10:00:00+02:00')
items.insert(date: DateTime.parse('2015-12-04T10:00:00+02:00'))

p items.all.map { |a| a[:date] }
# => [#<DateTime: 2015-12-04T11:00:00+01:00 ((2457361j,36000s,0n),+3600s,-Infj)>, #<DateTime: 2015-12-04T09:00:00+01:00 ((2457361j,28800s,0n),+3600s,-Infj)>]

as you can if I try to insert DateTime as a String PostgreSQL drops original timezone and uses it as it is. Is it expected behaviour or maybe I'm missing some extension to make it work? As far as I could see it works as expected with sqlite in memory DB.

[jeremyevans]

A couple issues here. First off, DateTime as a column type in a create_table creates a timestamp column, not a timestamptz column. For timestamp columns, PostgreSQL drops any timezone used. For your DateTime instance, Sequel converts the input timezone as expected using its timezone support. For your String instance, Sequel can do no conversion. You could add DB.extension :pg_timestamptz to make Sequel use timestamptz instead for DateTime columns. That will make it so the same timestamptz value is used for both inserts.

Second, even for timestamptz columns, PostgreSQL does not track the input timestamp. timestamptz columns are converted to UTC internally by PostgreSQL, and PostgreSQL will convert them to the session's timezone on output (see https://www.postgresql.org/docs/current/sql-set.html for controlling that). Because you are setting database and application timezone, Sequel will also convert the output timezone when retrieving records.

[nashby]

Alright, thanks for explanation! I indeed use timestamp without time zone in pg and don't expect it to save timezone. I was just wondering if Sequel could be smart enough to recognize that String and treat it as DateTime object with proper timezone conversion. But that's probably too much magic. I'll be using DateTime object then. Thanks!