sql - in postgres, can you set the default formatting for a timestamp, by session or globally?

Question

In Postgres, is it possible to change the default format mask for a timestamp?

right now comes back as

2012-01-03 20:27:53.611489

I would like resolution to minute like this:

2012-01-03 20:27

I know I can do this on individual columns with to_char() as or stripped down with a substr() by the receiving app, but having it formatted correctly initially would save a lot of work and reduce a lot of errors.

Answer 1

In PostgreSQL, The formatting of timestamps is independent of storage. One answer is to use to_char and format the timestamp to whatever format you need at the moment you need it, like this:

select to_char(current_timestamp, 'yyyy-MM-dd HH24:MI:SS.MS');

select to_timestamp('2012-10-11 12:13:14.123', 
     'yyyy-MM-dd HH24:MI:SS.MS')::timestamp;

But if you must set the default formatting:

Change the postgresql timestamp format globally:

Take a look at your timezone, run this as an sql query:

show timezone
Result: "US/Eastern"

So when you are printing out current_timestamp, you see this:

select current_timestamp
Result: 2012-10-23 20:58:35.422282-04

The -04 at the end is your time zone relative to UTC. You can change your timezone with:

set timezone = 'US/Pacific'

Then:

select current_timestamp
Result: 2012-10-23 18:00:38.773296-07

So notice the -07 there, that means we Pacific is 7 hours away from UTC. How do I make that unsightly timezone go away? One way is just to make a table, it defaults to a timestamp without timezone:

CREATE TABLE worse_than_fail_table
(
    mykey          INT unique not null,
    fail_date      TIMESTAMP not null
);

Then if you add a timestamp to that table and select from it

select fail_date from worse_than_fail_table
Result: 2012-10-23 21:09:39.335146

yay, no timezone on the end. But you want more control over how the timestamp shows up by default! You could do something like this:

CREATE TABLE moo (
    key     int PRIMARY KEY,
    boo     text NOT NULL DEFAULT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')
);

It's a text field which gives you more control over how it shows up by default when you do a select somecolumns from sometable. Notice you can cast a string to timestamp:

select '2012-10-11 12:13:14.56789'::timestamp
Result: 2012-10-11 12:13:14.56789

You could cast a current_timestamp to timestamp which removes the timezone:

select current_timestamp::timestamp
Result: 2012-10-23 21:18:05.107047

You can get rid of the timezone like this:

select current_timestamp at time zone 'UTC'
Result: "2012-10-24 01:40:10.543251"

But if you really want the timezone back you can do this:

select current_timestamp::timestamp with time zone
Result: 2012-10-23 21:20:21.256478-04

You can yank out what you want with extract:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

And this monstrosity:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'EST';
Result: 2001-02-16 20:38:40