Sub-second timestamps in Postgres

[Matt Allen]

Afternoon All;

Has anyone stored sub-second timestamps in Postgres? and then used them in rails? Any gotchas I should be looking out for?

Cheers,

Matta
Matt Allen
ma...@devlogic.com.au
0413 777 771

[Julio Cesar Ody]

Brief check using Ruby's Time#at, it seems to parse it wrong (output
of `+new Date` in a JS console).

Removing the milliseconds from it, parses it ok.

> --
> You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group.
> To post to this group, send email to rails-...@googlegroups.com.
> To unsubscribe from this group, send email to rails-oceani...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
>

[Matt Allen]

OK, turns out I was just not thinking it through.

if you were to hand in a decimal, like 1354246278.761 to rails and your DB column is a timestamp type. It Just Works™

Model.create column => Time.at(timestamp.to_f).utc

Will do it for you.

Sorry for the noise.

Cheers,

Matta
Matt Allen
ma...@devlogic.com.au
0413 777 771

[Iain Beeston]

I've had problems with different operating systems not supporting the same accuracy of timestamps. The details are hazy, but I think in ruby on mac timestamps are in milliseconds whereas on linux they're in nanoseconds. I think postgres is consistent about it, but your timestamps can get truncated when saving/loading from the database

Take a look at this, for example - http://marcricblog.blogspot.com.au/2010/04/who-cares-about-nanosecond.html

Iain

[Rob Dawson]

Yeah -- I found jruby and ruby 1.9 give different results running on ubuntu.  

Here are the specifics:

Jruby -- stores to milli seconds

Ruby 1.9 gave to nano seconds

Postgres was storing milli seconds.

It caused some fun in tests, but rounding the time to millis in ruby time.round(3) gave consistent testable results, and meant that times were not being truncated in the database. (instead truncating them early in ruby code)

Rob