The Crystal Programming Language Forum

Treatment of time in recent versions of crystal-db/crystal-pg; a bug?

I recently updated from crystal-db 0.5.1 and crystal-pg 0.16.1 to crystal-db 0.6.0 and crystal-pg 0.18. This caused the following change in the application behavior, which appears to be a bug.

Behavior with the newer versions of these packages
(1) I send an integer = 1565713800000 milliseconds since epoch to the server as part of JSON. This equals 12:30pm US Eastern time on August 13.
(2) On server I use timecreated: {type: Time?, converter: Time::EpochMillisConverter} as part of JSON.mapping to read this value.
(3) I insert the read value (which is now of type Time) into Postgres timestamptz column. The time in postgres shows up as 4:30pm US Eastern time. Postgres may be saving the timestamp, but in psql it displays the time locally (which is US Eastern time for me).
(4) As expected, when I get this value out again on another page, it is now 4 hours ahead of what I expect.

I reverted crystal-pg to 0.16.1 (which automatically changed crystal-db to 0.5.1). The behavior is as expected. When I send the integer = 1565713800000 milliseconds, and follow the above steps, the time in Postgres shows as 12:30pm US Eastern time on August 13.

What is strange is that both with the new as well as the old versions of these packages, the correct time gets inserted in postgres, when I use Time.now.

If this is a bug, I am not sure whether this is a problem with crystal-db or with crystal-pg.

I might be missing something, but https://github.com/will/crystal-pg/blob/master/src/pq/param.cr#L23 seems to have no timezone information.

Can you check if monkey patching this works for you?

struct PQ::Param
  def self.encode(val : Time)
    text val.to_utc.to_s("%Y-%m-%d %H:%M:%S.%6N")
  end
end

Oh, I might have broken it, not sure. Here’s the change I sent: https://github.com/will/crystal-pg/commit/e2f81912c9dcb689cf0a99c88d441e161e2c5606 . Apparently the old formatting included timezone… but I don’t know if postgres understands that?

Sorry for the basic question. Should I just add this code snippet to my code, and then run it with the new versions? Thanks for explaining.

@curious2learn you can paste that code in your application anywhere after require "pg". And check if that works.

From @asterite changes it’s probably the following patch the right fix.

struct PQ::Param
  def self.encode(val : Time)
    text val.to_s("%FT%X.%6N%z")
  end
end

[edit: without the to_utc]

Yay! The second fix works (as you said, the first one does not). Thank you. Would you recommend that I leave this one in my code for now, or is it better to use the older versions until this is fixed. Probably, I have to decide that, but would appreciate expert opinion.

Thanks!

Please report a bug in crystal-pg. Thank you!

Thank you. I will do it now.