Postgresql Interval, Date, Timestamp and Time Data Types

January 17, 2023

First we have the bare bones of the PostgreSQL Interval, Date and Timestamp Data types. Here are the questions:

  • What types are they? And what options do they have?
  • What postgresql.conf variables affect date and time i/o?

What are the Available Date and Time Data types?

Date
day of year using the Gregorian calendar.

Time [(p)]
time of day

Timestamp[tz] [(p)]
date and time with optional timezone.

Interval [fields] [(p)]
length of time, with optional units to restrict the type. Valid types are

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • YEAR TO MONTH
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE TO SECOND

This list includes [(p)] which is, for example (3). This means that the type has precision 3 for milliseconds in the value. ‘p’ can be 0-6, but the type must include seconds. ‘tz’ is a PostgreSQL short hand for ‘with time zone’.

Options available for date and time types

Variables affecting Date and Time data types in postgresql.conf:
TIMEZONE=’US/PACIFIC’- Accepted timezones are visible in the view pg_timezone_names.

DATESTYLE=’ISO, MDY’ - This display (client) form of dates. The first part is a choice of style and the second part is the ordering of Month, Day and Year.

INTERVALSTYLE=’POSTGRES’ -     There are four possible interval styles: sql_standard, postgres, postgres_verbose, ISO-8601.

Each of the above postgresql.conf variables can also be set in SQL using SET variable TO ‘value‘; In your client environment you may also set PGDATESTYLE to be one of the datestyles available,
e.g. PGDATESTYLE=’Postgres, mdy’.

From the Fine Manual:

Date Style values: Table 8.14. Date/Time Output Styles

STYLE SPECIFICATION DESCRIPTION EXAMPLE
ISO ISO 8601, SQL standard 1997-12-17 07:37:16-08
SQL traditional style 12/17/1997 07:37:16.00 PST
Postgres original style Wed Dec 17 07:37:16 1997 PST
German regional style 17.12.1997 07:37:16.00 PST

 There are three other date/time data types. These are ranges which consist of two individual values where the entire value is the left value up to the right value. It can be inclusive or exclusive of the end points. Inclusivity is specified as an endpoint with a square bracket ‘[‘ or ‘]’ and an endpoint using a parenthesis is exclusive of the endpoint.

TSRANGE range of timestamp without timezone
TSTZRANGE range of timestamp with timezone
DATERANGE range of date
=> create table dtranges ( ts tsrange, tstz tstzrange, dater daterange);
=> insert into dtranges (ts, tstz, dater) values (
            '[09-21-2019 10:45AM, 10-06-2019 23:59)', -- oktoberfest in germany
            '[07-16-1969 06:32 -7, 07-21-1969 17:54 -0 )', -- apollo 11
            '[01-01-1863, 06-19-1865)' -- emancipation proclamation declared
        );
=> select * from dtranges;
-[ RECORD 1 ]----------------------------------------------
ts    | ["2019-09-21 10:45:00","2019-10-06 23:59:00")
tstz  | ["1969-07-16 06:32:00-07","1969-07-21 10:54:00-07")
dater | [1863-01-01,1865-06-19)

Dates, Times, Intervals and Timestamps (oh, my!) are all stored in the database in timezone UTC. They are then displayed in the client’s style specification. For example if your server is in Germany, but your client is in New York, then the value you will see depends on the client style specifications above. If you are using timezone types, the timezone in the server will be treated according to the timezone specified in postgresql.conf or via SQL using AT TIME ZONE ”.

Input

It is fairly easy to input data. It doesn’t always go how I want, but it usually does. A recent example was to try to assign a time to ‘6am’. Darn, that is one of the ones that postgres doesn’t support. I had to use ‘6:00am’ instead. When in doubt, particularly for intervals, cast cast cast. The other thing that I like in the Fine Manual, are the tables which describe in detail the input of the data and time types. Instead of repeating them, I will include links here for your perusal.

Special input value constants are also available. Because these are constants, they must be enclosed in single quotes and remember that the values are identified when the SQL is read.

These input values are valid for date and timestamp. ‘now’ is also valid for time.

INPUT DESCRIPTION
EPOCH 1970-01-01 00:00:00+00 (Unix system time zero)
INFINITY later than all other time stamps
-INFINITY earlier than all other time stamps
NOW current transaction start time
TODAY midnight today
TOMORROW midnight tomorrow
YESTERDAY midnight yesterday

There are also a few variable looking functions that will give you the current date/time values. If you want to track everything in a transaction as the same time, use ‘now’, however, if you want the current time then use one of the appropriate functions.

  • current_date
  • current_time
  • current_timestamp
  • localtime
  • localtimestamp
  • now()
  • statement_timestamp()
  • timeofdate()
  • transaction_timestamp

More about functions shortly, but remember ‘now’, the constant, is transaction start time and the current_* functions are real time.

QUERY RETURNS
select ‘now’::time + ’27 seconds’; 12:40:27.915772
select ‘now’::time + ’27 hours’; 15:40:13.297067
select ‘now’::date + ’27 hours’; error
ERROR: operator is not unique: date + unknown
LINE 1: select ‘now’::date + ’27 hours’;
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
select ‘now’::date + ’27 hours’::interval; 2019-07-01 03:00:00

There are several different ways of doing the casts, for example interval ’32 hours 3 min’; I favor the PostgreSQL styled :: casting operators. However, this brings up another tricky point when doing arithmetic on intervals. It appears to be important to use fields in common where possible. In the first example below, we see the date field was changed. The result is awkward but correct. In the second, the field ‘hours’ was in common and so we got a clearer answer.

Easing into the next section early, let us see the justify functions, in case they can help with the awkwardness. Aww, the justify_days() have the same awkward problem with the – ‘1 day’, but the others work well.

QUERYRESULT
select interval ’32 hours 3 mins’ – ‘1 day’); -1 days +32:03:00
select interval ’32 hours 3 mins’ – ’24 hours’; 08:03:00
   
select justify_interval(interval ’32 hours 3 mins’ – ’24 hours’); 08:03:00
select justify_interval(interval ’48 hours 3 mins’) – ‘1 day’::interval; 1 day 00:03:00
select justify_days(interval ’48 hours 3 mins’) – ‘1 day’::interval; -1 days +48:03:00
select justify_hours(interval ’48 hours 3 mins’) – ‘1 day’::interval; 1 day 00:03:00
select justify_hours(interval ’84 hours 3 mins’ – ’24 hours’); 2 days 12:03:00

 

I’ll admit it. I fell down a rabbit hole putting together a good example for use of timestamp(tz), date, interval and time functionality. The example is a Fairfield Faux System Transit Tracking. (FFSTT–say it out loud.) All of the data is generated, but generated orderly to reflect a proper bus system. You will see some of this generated data as we go. A purchase and maintenance dates were assigned using the timestamp type and when you are doing arithmetic with intervals, the requirement of casting the interval is a bit fuzzy.

To generate bus data, I decided that there will be 25 buses, five per year. I’m just punting on the maintenance date, setting maintenance time to be the purchase date plus two months. The buses will be identified as 1001-1025.

Get first purchase date.
In a loop on each bus,
    if this is the sixth bus for this purchase date,
    then increment the purchase := purchase + '1 year'::interval,
    set the maintenance := purchase + '2 months'::interval
    insert into the buses table.

There is an perfectly ordinary set of operators and a rich set of functions for use of interval, timestamp(tz), date and time. The example of operators below should remind you that it is best to use the same units when doing arithmetic with date and time types.

OP arithmetic Returns OP arithmetic Returns
– interval negative interval + date + interval timestamp
  time – interval time   date + time timestamp
  interval – interval interval   interval + interval interval
  timestamp – timestamp interval   timestamp + interval timestamp
  date – date integer (days)   time + interval time
  date – interval timestamp * integer * interval interval
  time – time interval   double precision * interval interval
  timestamp – interval timestamp / interval/double precision interval

In addition to the current_* functions previously mentioned, here are examples of some of the additional functions available.

Common Date and Time Functions

Let us take a look at the bus company to see how the date and time functions behave in quasi-real life.

The first question from the management is on the age status of the buses. Specifically, how old are the buses we have, using age(timestamp)?
These three queries ask:

  • How old are the buses?
  • Which buses are more than five years old?
  • What is the average age of the buses?

 

select count(bus), age(purchase) from buses group by age(purchase) order by age(purchase);
 count |          age
-------+------------------------
     5 | 1 year 4 mons 14 days
     5 | 2 years 4 mons 14 days
     5 | 3 years 4 mons 15 days
     5 | 4 years 4 mons 14 days
     5 | 5 years 4 mons 14 days
(5 rows)

select bus, age(purchase) from buses where purchase < current_timestamp - '5 years'::interval order by bus;
 bus  |          age
------+------------------------
 1001 | 5 years 4 mons 14 days
 1002 | 5 years 4 mons 14 days
 1003 | 5 years 4 mons 14 days
 1004 | 5 years 4 mons 14 days
 1005 | 5 years 4 mons 14 days
(5 rows)

select avg(age(purchase)) from buses;
               avg
---------------------------------
 3 years 4 mons 14 days 04:48:00
(1 row)

The function date_part(‘text’,timestamp) returns a double precision number based on the units you request. For example, if you request ‘hour’ then you will get the hours as a double precision number. The function is equivalent to extract(field from timestamp), except the quotes on the unit and the requirement of from. The extract function is also allowed the form extract(field from interval);

More Questions

Management then wanted to know the difference between the actual arrival time and scheduled arrival time per route and stop. But they only cared about those close to one minute. This function date_part() allows you to do this in double precision arithmetic–after you subtract the scheduled from the actual. (Rules: All buses are a bit late. Lateness is accumulated through a route run. The city is laid in an ABC/123 grid.) The field rtstops is the sequential identifier of a stop on a route, as opposed to the formal, standalone stop number which could be a stop for any route.

select s.route, s.rtstop, c.xstr1||'x'||c.xstr2||'('||c.corner||')' as stop_street,
       t.arrival as act_arrival, s.arrival as sched_arrival,
       date_part('seconds',(t.arrival - s.arrival)) as sched_diff
from tracktime t join schedules s using (route, run, rtstop, stopno, corner)
join stops c using (stopno, corner)
where date_part('seconds',(t.arrival - s.arrival)) > 58 and s.run = 1
order by route, t.arrival, s.rtstop;

 route  | rtstop | stop_street |        act_arrival         | sched_arrival | sched_diff
--------+--------+-------------+----------------------------+---------------+------------
 DIA-2  |     14 | 13xM(e)     | 2019-06-22 07:29:40.691579 | 07:28:41.73   |  58.961579
 DIA-2  |     17 | 7xG(n)      | 2019-06-22 07:50:49.473361 | 07:49:51.36   |  58.113361
 DIA-2  |     13 | 15xO(n)     | 2019-06-24 07:22:38.22922  | 07:21:38.52   |   59.70922
 MID-ew |     14 | 10xM(n)     | 2019-06-24 07:29:41.234253 | 07:28:41.73   |  59.504253
 MID-ew |     17 | 10xG(n)     | 2019-06-24 07:50:49.964865 | 07:49:51.36   |  58.604865
 MID-ns |     16 | 9xJ(e)      | 2019-06-22 07:43:46.284443 | 07:42:48.15   |  58.134443
 MID-ns |     13 | 15xJ(e)     | 2019-06-24 07:22:36.706033 | 07:21:38.52   |  58.186033

This could also be done using intervals instead of double precision, but there is a little more cast jam required. This shows the first five stops in the DIA-1 route. In the query substitute date_part(‘seconds’,(t.arrival – s.arrival)) with ((t.arrival – t.rundate)::time – s.arrival)::interval

route  | rtstop | stop_street |   act_arrival   | sched_arrival |   sched_diff
--------+--------+-------------+-----------------+---------------+-----------------
 DIA-1  |      1 | 1xA(s)      | 05:57:01.488634 | 05:57:00      | 00:00:01.488634
 DIA-1  |      2 | 3xC(e)      | 06:04:04.795069 | 06:04:03.21   | 00:00:01.585069
 DIA-1  |      3 | 5xE(s)      | 06:11:10.535381 | 06:11:06.42   | 00:00:04.115381
 DIA-1  |      4 | 7xG(e)      | 06:18:22.281734 | 06:18:09.63   | 00:00:12.651734
 DIA-1  |      5 | 9xI(s)      | 06:25:25.691776 | 06:25:12.84   | 00:00:12.851776

And now we re-ask the question posed by Chicago, ‘Does anyone really know what time it is?’. Years, Months, Days, Hours, Minutes, Seconds–these can all be values of PostgreSQL date time data types. Logical manipulation can give the right time in the right timezone, but beware some parts are tricky.

Time flies like an arrow…Fruit flies like a banana.

Share this

More Blogs

PostgreSQL 16 Update: Grouping Digits in SQL

One of the exciting new features in PostgreSQL 16 is the ability to group digits in numeric literals by separating them with underscores. This blog post covers the details.
October 17, 2023