Timezones aren't persisted to the database (or rendered in hashes, json, etc)

[robacarp]

write = Model.new
write.user_id = 3_i64
write.timestamp_field = Time.now
write.save

puts "field before database: #{write.timestamp_field}"
puts "hash field before database: #{write.to_h["timestamp_field"]}"

puts "="*40

read = Model.find!(write.id)
puts "field after database: #{read.timestamp_field}"
puts "hash field after database: #{read.to_h["timestamp_field"]}"

puts "="*40

delta = Time.now - read.timestamp_field.not_nil!
puts "Time delta: #{delta}"

# field before database: 2018-06-08 17:07:34 -06:00
# hash field before database: 2018-06-08 17:07:34
# ========================================
# field after database: 2018-06-08 17:07:34 UTC
# hash field after database: 2018-06-08 17:07:34
# ========================================
# Time delta: 06:00:00.673049000

At a minimum timezones should be persisted to the database, but they should also be emitted into json in the right string format for javascript.

I'm not sure that Time objects should be stringified when a model is converted to a hash at all.

[Blacksmoke16]

The issue might be that Time objects do not support Timezones. Timezone support is coming in crystal 0.25.0

crystal-lang/crystal#5324

[robacarp]

@Blacksmoke16 interesting. I followed that pull for a while several months ago but gave up on it. If that’s true, why are there time zones on the timestamps in the output above? There’s some subtlety about the implementation Or details I think I’m missing.

[robacarp]

Adding a little note here about something to consider when implementing this: converting to UTC will solve a bunch of the use cases here, but not all of them. http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html

[Blacksmoke16]

@robacarp I was looking into this a bit more. From what i can tell (at least related to PG), is that PG doesnt support timezone specific datetime with timezone columns. For example using the TIMESTAMP data type:

t = Test.new
t.datetime = Time.now(Time::Location.load("America/New_York"))
pp t
t.save

pp Test.first!

Which prints (with extraneous fields removed):

#<Test:0x280df00
@datetime=2018-10-21 14:28:54.133870000 -04:00 America/New_York,
@id=nil>
#<Test:0x280de60
@datetime=2018-10-21 14:28:54.133000000 UTC,
@id=21_i64>

PG will take the given date and store it as is, but when returning it, no TZ data is given so crystal assumes its UTC.

There is the TIMESTAMPTZ data type that will take the time obj with tz, convert it to UTC, and store the UTC version of it in the database.

tl;dr it looks like only way to store a datetime with a specific tz is to use a string field and just parse that into a datetime obj. Or use the TIMESTAMPTZ and take the UTC and convert that into whatever tz you want to work in.

[robacarp]

Interesting. I wonder what other frameworks do.

[Blacksmoke16]

The other ORM i was using, just auto converts everything to UTC and stores/returns that.