Postgresql Interval, Date, Timestamp and Time Data Types

Does anyone really know what time it is?

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?

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’.

Variables affecting Date and Time data types in postgresql.conf

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

Built-in Ranges

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.

=> 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)

Date and Time Storage

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 an 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.

• Date and Time (and Timestamp) Input
• Interval Input
• Time Zone Input

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.

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.

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

Date, Time and Interval Arithmetic

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.

Fake Bus Purchase and Maintenance

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.

Date/Time Functions and Operators

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.

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 &lt 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)) &gt 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.

[email protected] GeneralBits for 2ndQuadrant PostgreSQL

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