Timestamps casted to nil?


#1

Hi,

for some reason, all timestamp fields with or without time zone in my
Postgres tables seem to be casted to nil. From console:

me = User.find(‘PS12345’)
=> #<User:0x2379788 @attributes={“mtime”=>“01.03.2006 13:26:32.737166”,
“valid”=>“8”, “valid_from”=>“01.03.1999 14:09:21 CET”,
“valid_thru”=>nil, “lastlogin”=>“13.03.2006 16:04:58”,
“password”=>“cbd8f7984c654c25512e3d9241ae569f”, “login”=>“PS12345”,
“email”=>“removed_email_address@domain.invalid”}>

puts me.valid_from
nil
=> nil

Using before_type_cast, I can see the content is still there, albeit as
string:

puts me.valid_from_before_type_cast
01.03.1999 14:09:21 CET
=> nil

Saving a modified object sets all timestamps to nil:

me.valid=0
=> 0

me.save
ActiveRecord::StatementInvalid: PGError: ERROR: null value in column
“valid_from” violates not-null constraint
: UPDATE users SET “email” = ‘removed_email_address@domain.invalid’, “mtime” = NULL,
“lastlogin” = NULL, “valid_from” = NULL, “valid” = 0, “password” =
‘cbd8f7984c654c25512e3d9241ae569f’, “valid_thru” = NULL WHERE login =
‘PS12345’

This happens for me with Ruby 1.8.2 and 1.8.4, Rails 1.0.0 on OS X and
Windows, with postgres-pr or ruby-postgres as gems (Postgres 8.1.2). I’d
be thankful for any ideas on why this happens …

Cheers,
Jan


#2

I think the problem is date format returned from server.
The odds are your server parameter DateStyle is set to German, so it
can’t be directly parsed using Date.parse or Time.parse.
While DateStyle is set to “ISO, MDY” everything should work fine.
You should either adjust ‘datestyle’ parameter in postgresql.conf and
restart PG server or call
ActiveRecord::Base.connection.execute(“SET DateStyle TO ISO, MDY”)


#3

After a quick check on the console, this seems to to have been the
cause. Since I cannot modify the server configuration itself, setting it
per-connection with connection.execute is the way to go for me.

Many thanks for your help, Nikolai!

  • Jan

#4

Sorry, I’m a bit wrong.
You can also do it w/o restarting server:
ALTER DATABASE your_db_name SET DateStyle TO ISO;
http://www.postgresql.org/docs/8.1/interactive/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT