Incorrect datetime value; Ruby on Rails

Question

I am running a test with mailers within Ruby on Rails, and I am getting the following errors:

*********-C02MGBVJFD57:myapp ************$ bundle exec rake test:mailers

# Running:

EE

Finished in 0.110500s, 18.0995 runs/s, 0.0000 assertions/s.

  1) Error:
UserMailerTest#test_account_activation:
ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect datetime value: '2014-12-18 01:07:49 UTC' for column 'activated_at' at row 1: INSERT INTO `users` (`name`, `email`, `password_digest`, `admin`, `activated`, `activated_at`, `created_at`, `updated_at`, `id`) VALUES ('Michael Example', '[email protected]', '$2a$04$hz6pvZctid6gZEuv0.qAe.0mEfbEjxso9GrXwC3yHRhIThIJ7Vx5m', 1, 1, '2014-12-18 01:07:49 UTC', '2014-12-18 01:07:49', '2014-12-18 01:07:49', 762146111)



  2) Error:
UserMailerTest#test_password_reset:
ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect datetime value: '2014-12-18 01:07:49 UTC' for column 'activated_at' at row 1: INSERT INTO `users` (`name`, `email`, `password_digest`, `admin`, `activated`, `activated_at`, `created_at`, `updated_at`, `id`) VALUES ('Michael Example', '[email protected]', '$2a$04$XTAWn5P9kikgOXqxQgiJqOjP027p4HnucHZ6pth2aaLwolaFcehsm', 1, 1, '2014-12-18 01:07:49 UTC', '2014-12-18 01:07:49', '2014-12-18 01:07:49', 762146111)


2 runs, 0 assertions, 0 failures, 2 errors, 0 skips

When I check my fixtures users.yml file, I see that I am using the time zone ruby stamp:

michael:
  name: Michael Example
  email: [email protected]
  password_digest: <%= User.digest('password') %>
  admin: true
  activated: true
  activated_at: <%= Time.zone.now %>

archer:
  name: Sterling Archer
  email: [email protected]
  password_digest: <%= User.digest('password') %>
  activated: true
  activated_at: <%= Time.zone.now %>

lana:
  name: Lana Kane
  email: [email protected]
  password_digest: <%= User.digest('password') %>
  activated: true
  activated_at: <%= Time.zone.now %>

mallory:
  name: Mallory Archer
  email: [email protected]
  password_digest: <%= User.digest('password') %>
  activated: true
  activated_at: <%= Time.zone.now %>

<% 30.times do |n| %>
user_<%= n %>:
  name:  <%= "User #{n}" %>
  email: <%= "user-#{n}@example.com" %>
  password_digest: <%= User.digest('password') %>
  activated: true
  activated_at: <%= Time.zone.now %>
<% end %>

and my db/seeds.rb file looks like so:

User.create!(name:  "Example User",
             email: "[email protected]",
             password:              "foobar",
             password_confirmation: "foobar",
             admin: true,
             activated: true,
             activated_at: Time.zone.now)

99.times do |n|
  name  = Faker::Name.name
  email = "example-#{n+1}@railstutorial.org"
  password = "password"
  User.create!(name:  name,
               email: email,
               password:              password,
               password_confirmation: password,
               activated: true,
               activated_at: Time.zone.now)
end

EDIT: Here are the tests

test/mailers/user_mailer_test.rb:

require 'test_helper'

class UserMailerTest < ActionMailer::TestCase

  test "account_activation" do
    mail = UserMailer.account_activation
    assert_equal "Account activation", mail.subject
    assert_equal ["[email protected]"], mail.to
    assert_equal ["[email protected]"], mail.from
    assert_match "Hi", mail.body.encoded
  end

  test "password_reset" do
    mail = UserMailer.password_reset
    assert_equal "Password reset", mail.subject
    assert_equal ["[email protected]"], mail.to
    assert_equal ["[email protected]"], mail.from
    assert_match "Hi", mail.body.encoded
  end
end

Is there something I am doing wrong? Thank you for your help.

michael hartl said sqlite3, however @Ruby_Pry 's answer is correct if you're using mysql. — moeabdol
– moeabdol, Commented May 2, 2015 at 18:28
In my case, I had a "date_time_formats" initializer which had Time::DATE_FORMATS[:db] set to '%d/%m/%Y %H:%M:%S' instead of '%Y/%m/%d %H:%M:%S'. — fabio.sang
– fabio.sang, Commented Jun 9, 2016 at 13:33

Ruby_Pry · Accepted Answer · 2014-12-18 04:16:12Z

4

Is activated_at typed as datetime in your migration file? You might want to try Time.zone.now.to_datetime.

answered Dec 18, 2014 at 4:16

Ruby_Pry

2371 silver badge10 bronze badges

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

4 Comments

user1072337 Over a year ago

I tried adding this to the seed file, but I am still getting the same error. Any other ideas?

user1072337 Over a year ago

any other help would be much appreciated

Ruby_Pry Over a year ago

My apologies, didn't see this for a while, did you end up fixing it?

user4710450 Over a year ago

@user1072337, its strange because seed data aren't used in tests, you should maybe check the fixtures.

Thomas Roest · Accepted Answer · 2015-08-16 08:41:35Z

2

or you can simply use a fixed created_at time in fixtures:

created_at: "2015-08-15 20:53:32"

answered Aug 16, 2015 at 8:41

Thomas Roest

3172 silver badges12 bronze badges

Comments

SamuelLJohnson · Accepted Answer · 2016-02-04 18:26:28Z

1

I am using MySQL 5.7.10. I was experiencing similar issues when attempting to insert records directly into a table using ModelName.connection.execute(SQL statement), including the created_at and updated_at timestamps.

Here is what worked for me:

'#{DateTime.now.strftime('%F %T')}'

Be sure to include the surrounding single quotes.

answered Feb 4, 2016 at 18:26

SamuelLJohnson

6194 silver badges16 bronze badges

Comments

Aditya Shedge · Accepted Answer · 2014-12-20 13:32:13Z

0

I suspect your column is of type 'datetime'. Use 'DateTime.now.in_time_zone' instead of 'Time.zone.now'

answered Dec 20, 2014 at 13:32

Aditya Shedge

3961 silver badge7 bronze badges

Comments

user2442202 · Accepted Answer · 2015-07-09 19:28:20Z

0

I've got same errors. But I've updated datetime part with Timezone.now.to_datetime in both seeds.rb and users.yml and then run:

$ bundle exec rake db:migrate:reset
$ bundle exec rake db:seed

and then run

$ bundle exec rake test

You suppose to get ALL GREEN.

answered Jul 9, 2015 at 19:28

user2442202

Comments

Steve Wollkind · Accepted Answer · 2016-11-15 14:53:39Z

0

ActiveRecord::Base.sanitize will take a ruby Time object and properly convert to a quoted sql value

ActiveRecord::Base.sanitize(DateTime.now)
=> "'2016-11-15 09:47:54'"

answered Nov 15, 2016 at 14:53

Steve Wollkind

616 bronze badges

Collectives™ on Stack Overflow

Incorrect datetime value; Ruby on Rails

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related