Postgresql – How to always get Timestamp in certain format without having to explicitly define it everytime

postgresqltimestamp

When I run a select on a timestamp I get something like:

2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM

In order to format properly I have to do:

SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS AM')
FROM analytics WHERE business_id = 371746;

which is kind of a pain, since I often just do SELECT * and don't specify the fields.

Is there a way to tell Postgres to always return this kind of format 'MM/DD/YYYY HH12:MI:SS AM' from a SELECT without having to specify it every time?

Best Answer

For general preferences you have the DateStyle and LC_TIME settings.

But it would be madness to always truncate fractional seconds automatically - hiding part of the relevant information.

A possible syntax shortcut would be to create a tiny function once:

CREATE OR REPLACE FUNCTION my_ts(timestamp)
  RETURNS text LANGUAGE sql IMMUTABLE AS 
$$SELECT to_char($1, 'MM/DD/YYYY HH12:MI:SS AM')$$;

Then:

SELECT my_ts(entered) FROM analytics WHERE business_id = 371746;

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;

PostgreSQL – How to Get the Current Unix Timestamp

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

PostgreSQL – How to Get the Current Unix Timestamp

Related Question