1

Here is a query result from Postgres:

$ psql ... -c 'select the_date from foo where foo_id in (998,999)'
     the_date      
------------------------
 2012-03-07 09:34:47.98
 2012-03-16 11:31:25.336

the_date is "timestamp without time zone".

Here is a Ruby program:

#!/usr/bin/env ruby

require 'sequel'

@DB = Sequel.connect({...})
query = "select the_date from foo where foo_id in (998,999)"
@DB[query].each do |row|
  warn row
end

and some output:

{:the_date=>2012-03-07 09:34:47 -0600}
{:the_date=>2012-03-16 11:31:25 -0500}

Where does the -0500 and -0600 come from? That is the "Olson timezone" of the server and the client machines (US/Central), but why does Ruby add it and psql does not?

I've been reading the docs, and I'm thoroughly confused.

The server is Postgres 9.0.4, the client is psql 9.1.4, sequel is 3.33.0.

dfrankow
  • 20,191
  • 41
  • 152
  • 214

2 Answers2

3

The column is of type 'timestamp without timezone'. Thus when Postgres displays a value in this column it just displays the timestamp with no timezone. However, Sequel wants to convert a Postgres timestamp to an instance of the Ruby Time class, and an instance of the Time class must have a timezone specified - either it's a time in the local timezone or it's a time in UTC. Thus Sequel must choose one. By default, it's choosing your local timezone.

You may configure the database and application timezone in Sequel. See https://www.rubydoc.info/gems/sequel/4.36.0/Sequel/Timezones

For example, here's the default Sequel behavior with a database I had handy:

>   c['select * from actors'].each do |row|; puts row[:created_at]; end
Thu Jul 12 20:33:17 -0400 2012

Here the timestamp is assumed to be in my local timezone (EDT).

However, if I do this:

> Sequel.database_timezone = :utc
 => :utc 
> c['select * from actors'].each do |row|; puts row[:created_at]; end
Thu Jul 12 20:33:17 UTC 2012

Then the timestamp is assumed to be in UTC.

Ross Attrill
  • 2,594
  • 1
  • 22
  • 31
Rich Drummond
  • 3,439
  • 1
  • 15
  • 16
  • Great answer, thanks. So Sequel's "local timestamp" behavior is sophisticated enough to take into account daylight savings time based on the timestamp itself (hence the -0500 and -0600)? – dfrankow Aug 02 '12 at 04:01
  • 1
    Yes -- although that sophistication is actually from Ruby's Time class. For example, for me in the Eastern zone, Time.parse('2012-03-07 09:34:47').to_s gives "Wed Mar 07 09:34:47 -0500 2012", while Time.parse('2012-03-16 11:31:25').to_s gives "Fri Mar 16 11:31:25 -0400 2012". – Rich Drummond Aug 02 '12 at 04:07
0

I've had some strange dealings with ruby and dates/times/timestamps. It looks like the program is trying to convert the result into a known datatype and automatically casting it, which is why you receive the 5digit timezone code at the end.

Kosmonaut
  • 2,154
  • 2
  • 18
  • 19