Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 758
G Sree Teja Simha Avatar asked Oct 06 '13 10:10

G Sree Teja Simha


People also ask

How does Postgres store timestamp with timezone?

For timestamp with time zone , the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT ). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.

Does Postgres timestamp have timezone?

Introduction to PostgreSQL timestamp The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.

How are timestamps stored in Postgres?

The TIMESTAMP (also known as TIMESTAMP WITHOUT TIME ZONE ) and TIMESTAMPTZ (also known as TIMESTAMP WITH TIME ZONE ) types stored as a 64-bit integer as a microsecond offset since 1970-01-01 in CRDB and as a 64-bit integer microsecond offset since 2000-01-01 in PostgreSQL (by default).

How do I find my Postgres time zone?

You can check all of the supported timezone names by using the following query: select * from pg_timezone_names; Let's exit our psql session, and reenter into it again.


2 Answers

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.

like image 117
Dimitri Avatar answered Oct 04 '22 23:10

Dimitri


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".

like image 28
Richard Huxton Avatar answered Oct 05 '22 00:10

Richard Huxton