Datetime field using Rails on Postgres

Question

I'm using Rails to make a scheduling app with Postgres. I have a model Shift with two timedate columns, start_at and end_at. The migration to create it follows:

class CreateShifts < ActiveRecord::Migration
  def change
    create_table :shifts do |t|
      t.references :user
      t.references :schedule
      t.datetime :start_at
      t.datetime :end_at
      t.string :position
      t.string :notes

      t.timestamps
    end
    add_index :shifts, :user_id
    add_index :shifts, :schedule_id
  end
end

When I try to create a record I get the following error 500 output:

GError: ERROR:  column "end_at" is of type timestamp without time zone but expression is of type time without time zone at character 132
HINT:  You will need to rewrite or cast the expression.
: INSERT INTO "shifts" ("created_at", "end_at", "notes", "position", "schedule_id", "start_at", "updated_at", "user_id") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"

I am creating a valid DateTime object before the DB save, because I've used the logger to display it and it is correct. The params look like this:

{"utf8"=>"✓",
 "authenticity_token"=>"qornHMTAdikZJP/sByPIg//fFuYHHAQH3M/0R9XnP+o=",
 "shift"=>{"user_id"=>"1",
 "start_at(1i)"=>"2011",
 "start_at(2i)"=>"11",
 "start_at(3i)"=>"14",
 "start_at(4i)"=>"00",
 "start_at(5i)"=>"01",
 "end_at(1i)"=>"2011",
 "end_at(2i)"=>"11",
 "end_at(3i)"=>"14",
 "end_at(4i)"=>"00",
 "end_at(5i)"=>"02",
 "position"=>"",
 "notes"=>""},
 "schedule_id"=>"1"}

However, I can create a record via console by setting both fields to Time.now.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

The error message is quite clear: Your $2 in the INSERT statement is of type time instead of type timestamp (datetime)

INSERT INTO "shifts"

("created_at", "end_at", "notes", "position", "schedule_id", "start_at", "updated_at", "user_id")

VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"

Emphasis mine. You must be mixing up parameters, $2 is obviously not the one you display at the end of your posting. Or you inadvertently cast it to time before assigning. The part where you assign $2 is not visible in the question, that is where the problem occurs, most likely.

Maybe some kind of typo like in your question where you mention timedate instead of datetime?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 14, 2011 at 5:00

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Zac Over a year ago

Actually I just needed to restart Thin. Even in dev mode, a migration to change the col from time to datetime hadn't taken effect, so it was trying to save a time to the field.

ryan2johnson9 Over a year ago

Same problem here - I just had to restart rails server - not sure why you accepted Erwin's answer - the answer seems to be your comment to restart your server.

Collectives™ on Stack Overflow

Datetime field using Rails on Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related