Rails casts timestamptz to UTC in Postgres

Question

I'm on Rails 7.0.4 and Postgres 15. I have a trigger that checks if a timestamp(6) with time zone is within a certain date range:

CREATE TABLE public.contracts (
    id bigint NOT NULL,
    starts_on date NOT NULL,
    ends_on date
);

CREATE TABLE public.time_entries (
    id bigint NOT NULL,
    contract_id bigint,
    "from" timestamp(6) with time zone,
    "to" timestamp(6) with time zone
);

And this is the trigger:

CREATE FUNCTION time_entries_contract_trigger() RETURNS trigger AS $time_entries_contract_trigger$
BEGIN
  IF EXISTS (SELECT 1 FROM contracts WHERE contracts.id = NEW."contract_id" AND NEW."from"::date < starts_on) THEN
    RAISE EXCEPTION 'from % is before contracts.starts_on', NEW."from";
  END IF;
  IF EXISTS (SELECT 1 FROM contracts WHERE contracts.id = NEW."contract_id" AND NEW."to"::date > ends_on) THEN
    RAISE EXCEPTION 'to % is after contracts.ends_on', NEW."to";
  END IF;
  RETURN NEW;
END;
$time_entries_contract_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER time_entries_contract_trigger BEFORE INSERT OR UPDATE ON time_entries
  FOR EACH ROW EXECUTE PROCEDURE time_entries_contract_trigger();

And I set the default timezone like this:

module App
  class Application < Rails::Application
    # ...
    config.time_zone = "Europe/Berlin"
    # ...
end

The problem now is, that whenever I try to store a TimeEntry, Rails converts it to UTC and I get an error like this:

> contract = Contract.create(:contract, starts_on: Date.parse("2021-12-19"))
> contract.time_entries << build(:time_entry, from: Time.zone.parse("2021-12-19T00:00"))
ActiveRecord::StatementInvalid:
  PG::RaiseException: ERROR:  from 2021-12-18 23:00:00+00 is before contracts.starts_on
    CONTEXT:  PL/pgSQL function time_entries_contract_trigger() line 4 at RAISE

It makes sense insofar, that Rails converts Time.zone.parse("2021-12-19T00:00") to UTC and my trigger just casts it to a date with NEW."from"::date, just returning the date part (which now is 2021-12-18 and not 2021-12-19).

My questions now are:

How could I force Rails to store the columns with the timezone information in it? My Postgres columns are already timezone aware, so I guess converting it to UTC doesn't make sense any more? Or are there any drawbacks that I'm missing?

You're falling victim to a common missconception here. `timestampz` doesn't store a timezone - instead it converts the time into UTC when you insert the data. When you query the column PG converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection. Postgres doesn't actually have a type (built-in) which stores both a timestamp and timezone in one column and if you need to do that you would have use two columns. — max, Dec 20 '22 at 10:37
Its easy to make this mistake as the Postgres docs even describes the type as *with time zone*. https://stackoverflow.com/a/5876276/544825 — max, Dec 20 '22 at 10:38

Rails casts timestamptz to UTC in Postgres

0 Answers0