Skip to content

Time zone offset being subtracted from utc timestamp when inserting into column while using prepared statements #1094

Open
Open
Time zone offset being subtracted from utc timestamp when inserting into column while using prepared statements#1094

Description

@skunkworker
skunkworker
opened on Oct 8, 2021
rails 6.1.4.1
activerecord-jdbcpostgresql-adapter (61.1-java)
activerecord-jdbc-adapter (61.1-java)
jdbc-postgres (42.2.14)

jruby 9.2.19.0 on openjdk8

application.rb

config.time_zone = "UTC"

Table "public.foobars"
 Column            |            Type             | Collation | Nullable |                     Default
--------------------------------------------------------+-----------------------------+-----------+----------+--------------------------------------------------
 id                | integer                     |           | not null | nextval('foobars_id_seq'::regclass)
 created_at        | timestamp without time zone |           |          |
 updated_at        | timestamp without time zone |           |          |

When prepared statements are turned on and a record is inserted into the database, the active record debug log shows the correct UTC timestamp with an offset of +00:00.

[DEBUG] [2021-10-08 13:13:20 -0600] Foobar Create (31.2ms)  INSERT INTO "foobars" ("created_at", "updated_at") VALUES (?, ?)  [["created_at", Fri, 08 Oct 2021 19:13:20.573182000 UTC +00:00], ["updated_at", Fri, 08 Oct 2021 19:13:20.573363000 UTC +00:00], ]

However the inserted record takes my current offset (MDT -6:00) and applies it to the record, saving the incorrect time.

app_development=# select * from foobars;
-[ RECORD 1 ]------------------------------------------+-----------------------------------------

created_at                                             | 2021-10-08 13:13:14.411114
updated_at                                             | 2021-10-08 13:13:14.411256

Turning off prepared statements solves this problem.

Activity

  1. skunkworker
    changed the title [-]Time zone offset being subtracted from timestamp when inserting into timestamp without time zone field while using prepared statements true[/-] [+]Time zone offset being subtracted from utc timestamp when inserting into column while using prepared statements[/+] on Oct 8, 2021

  2. skunkworker commented on Oct 11, 2021

    @skunkworker
    skunkworker
    on Oct 11, 2021
    ContributorAuthor

    Looking into this more, the current timezone in my postgres instance is America/Denver. But once again, MRI does not have this issue using the pg adapter but JRuby does.

    psql (13.4)
Type "help" for help.

testdb=# show timezone;
    TimeZone
----------------
 America/Denver
(1 row)

  3. skunkworker commented on Oct 11, 2021

    @skunkworker
    skunkworker
    on Oct 11, 2021
    ContributorAuthor

    I suspect this isn't a commonly run into issue, because most if not all production databases would be running on a host in UTC time and postgres set to UTC.

  4. akimd commented on Jun 29, 2022

    @akimd
    akimd
    on Jun 29, 2022

    Maybe not common, but does happen. We have been hit. It results in a big mess :(

  5. thre-raritan commented on Dec 6, 2024

    @thre-raritan
    thre-raritan
    on Dec 6, 2024

    Hi, we are also affected by this issue. As a workaround, we disabled prepared statements; but a fix for this would be great.

  6. headius commented on May 23, 2026

    @headius
    headius
    on May 23, 2026
    Member

    @rsim Did you mention a similar issue you have been seeing?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions