Rails postgres Time attribute not saving

Question

Am I going insane? time attributes lose their value and reset to January 1 2000 after saving the active record object. The db is Postgres. Rails 5.2 and pg gem 1.1.3

2.6.6 :019 > obj.end_time = Date.new(2019, 9,9)
 => Mon, 09 Sep 2019 
2.6.6 :020 > obj.save
 => true 
2.6.6 :021 > obj.end_time
 => Mon, 09 Sep 2019 00:00:00 UTC +00:00 
2.6.6 :022 > obj.end_time.class
 => ActiveSupport::TimeWithZone

But now if I reload from the db, the value is reset:

2.6.6 :023 > obj.reload
2.6.6 :024 > obj.end_time
 => Sat, 01 Jan 2000 00:00:00 UTC +00:00
2.6.6 :025 > obj.end_time.class
 => ActiveSupport::TimeWithZone

The postgres column type is shown in psql as "time without time zone". I guess I just need to specify datetime instead of time in migrations if I want to set the date part?

I tried to change column type in a migration change_column :school_courses, :start_time, :datetime but got:

PG::DatatypeMismatch: ERROR: column "start_time" cannot be cast automatically to type timestamp without time zone HINT: You might need to specify "USING start_time::timestamp without time zone"

Not sure how to do this

I cannot reproduce this with a basic model, are you doing something in your model class which might affect this? — Eyeslandic
– Eyeslandic, Commented Sep 11, 2020 at 16:08
Must be something to do with the column type in Postgres. I've nothing in the model and it's happening elsewhere. Will try check in psql — rigyt
– rigyt, Commented Sep 11, 2020 at 17:10
Then it would be helpful for you post the model and the postgres table definitions (DDL). — Belayer
– Belayer, Commented Sep 11, 2020 at 17:21
In Rails the schema just says "time" for the column. Running \d in psql on the table gives the column type as "time without time zone" as I mentioned. Is there another psql command you recommend that might give more information? — rigyt
– rigyt, Commented Sep 11, 2020 at 17:40
@rigyt can you please post how you wrote your working migration (solution)? — thefonso
– thefonso, Commented Sep 17, 2021 at 0:17

rigyt · Accepted Answer · 2021-09-17 06:18:03Z

0

Use datetime column type to hold a date and time Only use time in the migration if you don't need the date (only want to store time part)

This is the case with postgres anyway

Migration example:

create_table "school_years" do |t|
    t.integer "year"
    t.integer "minimum_candidates_per_exam"
    t.time "am_start_time"
    t.time "pm_start_time"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

Although I generally just use datetime and set the year part as well, and avoid using time

edited Sep 17, 2021 at 6:18

answered Sep 24, 2020 at 9:23

rigyt

2,3634 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thefonso Over a year ago

Thank you for responding. Did you ever use the hint described "HINT: You might need to specify "USING start_time::timestamp without time zone"" inside the migration itself with the USING keyword?

Collectives™ on Stack Overflow

Rails postgres Time attribute not saving

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related