Postgresql Create Table Fieldname Timestamp with Time Zone UTC

postgresql timestamp create-table
Rich_F picture Rich_F · Mar 23, 2017 · Viewed 20.8k times · Source

What is the syntax for creating a table with a field with the UTC time zone?

I have for the fields:

(id INT PRIMARY KEY NOT NULL, 
 bravo timestamp without time zone DEFAULT now(),
 charlie timestamp with time zone UTC DEFAULT now()
)

This last field named charlie is not taking for some reason. I was hoping it was easy as just telling it had a time zone, then shoving UTC in there, and having the db figure out now() during input.

Answer

Laurenz Albe picture Laurenz Albe · Mar 23, 2017

I think you want this:

charlie timestamp without time zone NOT NULL
   DEFAULT (current_timestamp AT TIME ZONE 'UTC')

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
Difference between timestamps with/without time zone in PostgreSQL

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?

Read More
Using current time in UTC as default value in PostgreSQL

I have a column of the TIMESTAMP WITHOUT TIME ZONE type and would like to have that default to the current time in UTC. Getting the current time in UTC is easy: postgres=# select now() at time zone 'utc'; timezone …

Read More