Wrong time returned for timestamptz with :local default_timezone around DST time change #2147

Activity

1. yahonda commented on Feb 16, 2021

   

   yahonda

   on Feb 16, 2021

   Collaborator

   More actions

   Thanks for the report. I have attempted to reproduce it but it looks like I'm getting OCIError: ORA-01843: not a valid month not Time is offset by 1 hour..

   $ more rep2147.rb
   # frozen_string_literal: true
   
   require "bundler/inline"
   
   gemfile(true) do
     source "https://rubygems.org"
   
     git_source(:github) { |repo| "https://github.com/#{repo}.git" }
   
     gem "rails", github: "rails/rails", branch: "6-0-stable"
     gem "activerecord-oracle_enhanced-adapter",  github: "rsim/oracle-enhanced", branch: "release60"
     gem "rspec"
   
     platforms :ruby do
       gem "ruby-oci8"
     end
   end
   
   require "active_record"
   require "rspec/autorun"
   require "logger"
   require "active_record/connection_adapters/oracle_enhanced_adapter"
   
   # Set Oracle enhanced adapter specific connection parameters
   DATABASE_NAME = ENV["DATABASE_NAME"] || "dev"
   DATABASE_HOST = ENV["DATABASE_HOST"]
   DATABASE_PORT = ENV["DATABASE_PORT"]
   DATABASE_USER = ENV["DATABASE_USER"] || "oracle_enhanced"
   DATABASE_PASSWORD = ENV["DATABASE_PASSWORD"] || "oracle_enhanced"
   DATABASE_SYS_PASSWORD = ENV["DATABASE_SYS_PASSWORD"] || "admin"
   
   CONNECTION_PARAMS = {
     adapter: "oracle_enhanced",
     database: DATABASE_NAME,
     host: DATABASE_HOST,
     port: DATABASE_PORT,
     username: DATABASE_USER,
     password: DATABASE_PASSWORD
   }
   
   ActiveRecord::Base.logger = Logger.new(STDOUT)
   Time.zone = ActiveSupport::TimeZone.new('Central Time (US & Canada)')
   
   describe "bug test" do
     before(:all) do
       ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
       ActiveRecord::Schema.define do
         create_table :posts, force: true do |t|
           t.timestamptz :posted_at
         end
       end
   
       class Post < ActiveRecord::Base
       end
     end
   
     it "should properly store/retrieve timestamp with tz in :local" do
       ActiveRecord::Base.default_timezone = :local
       time = Time.new(2014, 11, 2, 1, 0, 0, '-05:00')
       post = Post.create!(posted_at: time)
       post.reload
       expect(post.posted_at).to eq time
     end
   
     it "should be able to properly format times for timestamptz column" do
       ActiveRecord::Base.default_timezone = :utc
       time = Time.now
       post = Post.create!(posted_at: time)
   
       expect(Post.where('posted_at = ?', time).first).to eq post
     end
   end

   $ ruby rep2147.rb
   ... snip 
   
   Failures:
   
     1) bug test should be able to properly format times for timestamptz column
        Failure/Error: expect(Post.where('posted_at = ?', time).first).to eq post
   
        ActiveRecord::StatementInvalid:
          OCIError: ORA-01843: not a valid month
        # stmt.c:265:in oci8lib_270.so
        # rep2147.rb:70:in `block (2 levels) in <main>'
        # ------------------
        # --- Caused by: ---
        # OCIError:
        #   ORA-01843: not a valid month
        #   stmt.c:265:in oci8lib_270.so
   
   Finished in 1.49 seconds (files took 0.24557 seconds to load)
   2 examples, 1 failure
   
   Failed examples:
   
   rspec rep2147.rb:65 # bug test should be able to properly format times for timestamptz column
   
   $

   @ioev Would you advise what is wrong with my testcase?
2. ioev commented on Feb 16, 2021

   

   ioev

   on Feb 16, 2021

   ContributorAuthor

   More actions

   Actually, that IS the behavior on the second test currently, because the timestamp isn't formatted correctly for the timestamptz column. I probably should have written the test a little more semantically with expect {}.not_to raise_exception.

   Looks like the first test is passing for you however, so maybe something else is missing in order to configure the test to use a timezone that has DST.
3. ioev commented on Mar 11, 2021

   

   ioev

   on Mar 11, 2021

   ContributorAuthor

   More actions

   Since we couldn't really wait, for the time being I've fixed the second problem by formatting all dates:
   timestamp '%Y-%m-%d %H:%M:%S %:z'

   Which seems to be accepted by the date field and the timestamp_with_tz, and doesn't seem to affect index choices as far as I can tell so far.
4. toffeegryphon commented on Jul 25, 2025

   

   toffeegryphon

   on Jul 25, 2025

   More actions

   I'd like to revive this thread - we are facing the same issue as well, and it's because the serialization drops the TZ component from the TimeWithZone: https://github.com/rsim/oracle-enhanced/blob/master/lib/active_record/type/oracle_enhanced/timestamptz.rb. We got around it by using a different coder to serialize:

   module TimestamptzOracleCoder
   
     FORMAT = '%Y-%m-%d %H:%M:%S %:z'
   
     def self.dump(value)
       value = DateTime.iso8601(value) if value.is_a?(String)
       value.strftime(FORMAT) unless value.nil?
     end
   
     def self.load(value)
       if value.is_a?(DateTime) or value.is_a?(Time)
         value
       else
         DateTime.strptime(value, FORMAT) unless value.nil?
       end
     end
   end
   
   class Model < ApplicationRecord
   
   serialize datetime_field, TimestamptzOracleCoder
   

   I believe the change should be to perform the String casting with the format suggested in the timestamptz class itself.
5. toffeegryphon commented on Jul 25, 2025

   

   toffeegryphon

   on Jul 25, 2025

   Last edited by toffeegryphon

   More actions

   Part of the problem also lies in

   oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/jdbc_connection.rb

   Line 517 in 820831d

   ts = rset.getTimestamp(i)

   . It uses getTimestamp(), which drops the timezone component. It should be using getString(), then strptime with the nls_timestamp_tz_format (or, just return a string, and have the downstream Type.deserialize` do the appropriate conversion. I imagine there may be some performance considerations, but at present it's just incorrect.

   As a side note, I think it shouldn't be using ActiveRecord.default_timezone, but the time_zone property of the connection? Theoretically speaking, these could differ.

   Using the raw getTimestamp() is the actual issue. It is converting it to the JVM time zone, which may differ from the ruby (default UTC) timezone.