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.

Solution 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

Solution 2

to_char() is used to create a string literal. If you want a different timestamp value, use:

date_trunc('minute', now())

For an input mask you can use:

to_timestamp('2012-01-03 20:27:53.611489', 'YYYY-MM-DD HH24:MI')

Cast to timestamp without time zone by appending ::timestamp.

To my knowledge, there is not setting in PostgreSQL that would trim seconds from timestamp literals by default.

Comments

[cc young]

thanks, but the issue is not the value of the timestamp but rather its string representation when selected. in this app, a timestamp presented to the user is always rounded to the nearest minute - having to do with display, not with data. was looking for a way to do this without a to_char() on each column of each select.

[Erwin Brandstetter]

@ccyoung: If you want to round to the nearest minute, to_char() connot help you either. You can only truncate with it. Anyway, this sounds like a job for the client, not for the database. a_horse_with_no_name is on the right track.

[cc young]

theoretically I'm sure you're probably right, but as a matter of practice have almost always put date formatting and arithmetic in SQL since it's so much better at it than javascript or php.

[cc young]

btw, insightful about round vs truncate with to_char() - fortunately this is the behavior expected

[MrR]

Does not answer the question.