Rails:How to create a time column with timezone on postgres

Question

I have a rails application which works on postgres db. I have a model called UserTimings where there are two fields from_time and to_time.

t.time     "from_time" t.time     "to_time" 

I expected that the time will be stored in complete UTC format with timezone information. After a little while, I realized that the database has this kind SQL query to create the table.

from_time time without time zone, to_time time without time zone, 

I do not want this. I want to store with time zone. I want the +0530 thingy in the UTC time which I'm not getting althought the rails application has been configured to handle it. Please tell me how to write a migration to force the db to use the time zone.

Thank you.

Answer 1

That migration do exactly what you want

class CreatePeppers < ActiveRecord::Migration   def change     create_table :peppers do |t|       t.column :runs, 'timestamp with time zone'       t.column :stops, 'time with time zone'     end   end end 

Types are for your choice. You can write here any type, that postgresql supports. But there may be problems with conversion to types, that rails can understand.

Answer 2

You seem to be confused about two separate things here - both common mistakes, almost everyone makes at least one of them.

Firstly "UTC format with timezone information" isn't specifying a time. It's specifying time AND place.

Secondly PostgreSQL's "timestamp with time zone" doesn't in fact store a time zone. It stores a UTC timestamp but it accepts a time zone. A better choice of name would be something like "absolute time". You can compare two of these values directly.

A timestamp without time zone doesn't actually give you a time unless you also have a place. You can't compare two of these unless you know which timezone each is in.

It sounds like what you want is a timestamp without time zone and a separate timezone. That way you can say "2pm on Tuesday, London Time".