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