Time intervals with PostgreSQL and TypeORM

JavaScript Node.js SQL

In the previous article, we’ve looked into various ways to store the date and time with PostgreSQL and TypeORM. Postgres can also manage intervals. With them, we can store a period of time.

Ways to store and display intervals in PostgreSQL

There are various ways we can input and view interval values. By default, PostgresSQL represents intervals using a format called postgres. We can check it by viewing the IntervalStyle parameter.

postgres

It includes the interval specified explicitly with years, months, days followed by the time in the hh:mm:ss format.

Above we use the :: sign to convert a string to an interval.

We can also specify microseconds, milliseconds, weeks, decades, centuries, and millennia.

The crucial thing is that we can use the - sign to negate a part of our value.

Instead of doing that, we can also use the ago keyword to negate all parts of the date.

Doing that gives us a negative interval.

postgres_verbose

The second format is postgres_verbose, where the hh:mm:ss format is replaced with explicitly stated hours, minutes, and seconds. The crucial thing is that we can use

To change IntervalStyle to postgres_verbose, run SET IntervalStyle = 'postgres_verbose';

Please notive that we can use abbreviations, such as min instead of minute.

When dealing with the postgres_verbose format, the same rules apply when dealing with negative intervals as with the postgres style.

iso_8601

Another format that we can find is iso_8601. ISO 8601 is an international standard of representing dates and times, and there’s a high chance you’ve already encountered it. Aside from dates and times, it also specifies a format for displaying intervals.

It starts with a letter P followed by the interval value. The time part is preceded by the letter T.

Please notice that we can use the M letter either to indicate minutes or months depending on whether we use it before or after the T letter.

To create a negative interval, we need to use the - sign before each part of the interval we want to negate.

sql_standard

We also have the sql_standard interval output format. Using it produces an output matching SQL standard interval literals.

First, we specify the years and months separated by a dash. After that, we specify the days and time separated by spaces.

To create a negative interval, we need to use the - sign instead of + next to each section that we want to negate. Please note that using the - sign at the beginning negates both years and months.

A thing worth remembering is that setting the IntervalStyle to one of the above styles only changes the output format. We can still input the interval in any style we want to.

Functions and operations with intervals

With intervals, we can perform a variety of operations. For example, we can add them or subtract them from dates.

If you want to know more about dates in Postgres, check out Managing date and time with PostgreSQL and TypeORM

Similarly, we can subtract and add intervals to each other.

We can also use regular numbers to multiple and divide the intervals.

Using intervals with TypeORM

To understand how to use intervals with TypeORM and TypeScript properly, we need to dive into some of the TypeORM’s dependencies.

Under the hood, TypeORM uses the pg library, which is a PostgreSQL client for Node.js. One of its dependencies is pg-types, a package that turns the raw data from Postgres into JavaScript types. Under the hood, it uses the postgres-interval library to parse intervals. It also exports an interface that we should use when using the interval column.

As of today, TypeORM comes with version 1.2.0 of the postgres-interval library, which is definitely not the most up-to-date, unfortunately. To understand how this package works in this version, let’s look into its internals.

Above, we can see that we can access various parts of the interval easily through properties.

Aside from the properties, we also have some methods. The first of them, toPostgres(), converts the interval to a string in the postgres format described at the beginning of this article.

Both other methods, toISO() and toISOString(), work the same in version 1.2.0 of the postgres-interval library. They convert the interval to a string in the iso_8601 format that we’ve discussed previously.

If you want to know more about prototypes, check out this article.

Doing operations on intervals in JavaScript

Currently, there are no functionalities built into JavaScript to manage intervals. The best approach would be to use the toISOString() method described above and pass the output to a date-management library.

There are quite a few packages that support durations. Some examples worth noting are:

• Luxon
  Duration.fromISO('P1Y2M3DT5H1M10S')
• dayjs
  dayjs.duration('P1Y2M3DT5H1M10S')
• moment.js
  moment.duration('P1Y2M3DT5H1M10S')

When we pass the ISO string into one of the above packages, we can perform various operations using functions built into our library of choice.

Summary

In this article, we’ve looked into the interval data type in PostgreSQL. It included looking into various inputting styles, displaying intervals in Postgres, and performing various operations on them. We’ve also learned how to define interval columns in TypeORM and manage the data returned to us by the postgres-interval library.