Date Time Operations

PostgreSQL supports all types of date, time, and timestamp data types, and operations on them.

Date/Time Types

Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. If there is any ambiguity, then the DateStyle parameter can be set to a required format (MDY or DMY, or YMD).

Date Input

Parameter DateStyle dictates the input date format. Check the current DateStyle format.

show datestyle;

It dictates that the input date should be in format month-date-year format or any of its close variations but in the same order. Trying to input the date in some other order will give an error. Let’s try these commands:

select '20/12/2016'::date; -- error

select '12/20/2016'::date; -- correct

select '12-20-2016'::date; -- correct

select '12:20:2016'::date; -- error

Parameter DateStyle can be changed at the session level, database level, or instance level.

Session Level

At the session level, it can be changed by setting the environment variable PGDATESTYLE or SET command.

• Using Environment Variable

export PGDATESTYLE="ISO, DMY";

psql -p 5433

select '20/12/2016'::date;

• Using SET command

show datestyle;

SET datestyle TO "ISO, DMY";

show datestyle;

select '20/12/2016'::date;

select '12/20/2016'::date;

There are other options as well for SET datestyle command.

SET DATESTYLE TO Postgres, MDY;

SET DATESTYLE TO SQL;

SET DATESTYLE TO German;

Database Level

Parameter Datestyle can be changed at the individual database level inside a cluster using ALTER DATABASE command. But, to bring the change into effect, the whole cluster has to be restarted.

Don’t forget to unset PGDATESTYLE as it overrides the database/instance level value of parameter DateStyle.

\c appdb

alter database appdb set datestyle TO "ISO, DMY";

\q
pg_ctlcluster 13 mars stop

pg_ctlcluster 13 mars start

unset PGDATESTYLE

psql -p 5433

select '20/12/2016'::date;

select '12/20/2016'::date;

Instance Level

Parameter Datestyle can be changed at the instance level by changing the parameter in ‘postgresql.conf’ file followed by a restart of the cluster.

show datestyle;

select now();

alter system set datestyle='German';

pg_ctlcluster 13 mars stop

pg_ctlcluster 13 mars start

unset PGDATESTYLE

psql -p 5433

select '20/12/2016'::date;

select '12/20/2016'::date;

Time Input

Time can be input in any of the general formats.

select '10:00:00'::time;

select '22:00:00'::time;

select '10:00:00 AM'::time;

select '10:00:00 PM'::time;

Timestamp Input

Timestamp can be input in any of the general formats.

select '31.03.2017 09:30:20'::timestamp;

select '31.03.2017 19:30:20'::timestamp;

select '31-03-2017 19:30:20'::timestamp;

select '31-03-2017 11:30:20 AM'::timestamp;

select '31-03-2017 11:30:20 PM'::timestamp;

Normal strings can be converted to date or systimestamp using in-built functions, and vice versa. Let’s see those in action:

Template Patterns for Date/Time Formatting

Converting string to date/timestamp

select to_date('2017-04-03', 'YYYY-MM-DD');

select to_date('2017-04-03 10:00:00 AM', 'YYYY-MM-DD');

select to_date('2017-04-03 10:00:00 AM', 'YYYY-MM-DD HH24:MI:SS');

select to_date('2017-04-03', 'YYYY-MM-DD HH24:MI:SS');

select to_timestamp('2017-04-03', 'YYYY-MM-DD');

select to_timestamp('2017-04-03 10:00:00 AM', 'YYYY-MM-DD');

select to_timestamp('2017-04-03 10:00:00 AM', 'YYYY-MM-DD HH24:MI:SS');

select to_timestamp('2017-04-03', 'YYYY-MM-DD HH24:MI:SS');

--MS is millisecond (000-999), will take last 3 digits from the millisecond input field.
select to_timestamp('2017-04-03 10:00:00:0000120 AM', 'YYYY-MM-DD HH24:MI:SS:MS');

--US is microsecond (000000-999999), will take last 6 digits from the millisecond input field.
select to_timestamp('2017-04-03 10:00:00:00234120 AM', 'YYYY-MM-DD HH24:MI:SS:US');

Put above statements into a file date.sql and run on ‘psql’ prompt. The output would be as below:

postgres=# \i date.sql
  to_date
------------
 03.04.2017
(1 row)

  to_date
------------
 03.04.2017
(1 row)

  to_date
------------
 03.04.2017
(1 row)

  to_date
------------
 03.04.2017
(1 row)

      to_timestamp
-------------------------
 03.04.2017 00:00:00 IST
(1 row)

      to_timestamp
-------------------------
 03.04.2017 00:00:00 IST
(1 row)

      to_timestamp
-------------------------
 03.04.2017 10:00:00 IST
(1 row)

      to_timestamp
-------------------------
 03.04.2017 00:00:00 IST
(1 row)

        to_timestamp
----------------------------
 03.04.2017 10:00:00.12 IST
(1 row)

         to_timestamp
-------------------------------
 03.04.2017 10:00:00.23412 IST
(1 row)

postgres=#

Converting date/timestamp to string

select to_date('2017-04-03', 'YYYY-MM-DD');

select to_date('2017-04-03 10:00:00 AM', 'YYYY-MM-DD');

select to_date('2017-04-03 10:00:00 AM', 'YYYY-MM-DD HH24:MI:SS');

select to_date('2017-04-03', 'YYYY-MM-DD HH24:MI:SS');

select to_timestamp('2017-04-03', 'YYYY-MM-DD');

select to_timestamp('2017-04-03 10:00:00 AM', 'YYYY-MM-DD');

select to_timestamp('2017-04-03 10:00:00 AM', 'YYYY-MM-DD HH24:MI:SS');

select to_timestamp('2017-04-03', 'YYYY-MM-DD HH24:MI:SS');

--MS is millisecond (000-999), will take last 3 digits from the millisecond input field.
select to_timestamp('2017-04-03 10:00:00:0000120 AM', 'YYYY-MM-DD HH24:MI:SS:MS');

--US is microsecond (000000-999999), will take last 6 digits from the millisecond input field.
select to_timestamp('2017-04-03 10:00:00:00234120 AM', 'YYYY-MM-DD HH24:MI:SS:US');