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 . We can check it by viewing the parameter.

postgres

It includes the interval specified explicitly with years, months, days followed by the time in the 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 , where the format is replaced with explicitly stated hours, minutes, and seconds.

To change IntervalStyle to , run

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

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

iso_8601

Another format that we can find is . 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 followed by the interval value. The time part is preceded by the letter .

YearsY
Months / MinutesM
WeeksW
DaysD
HoursH
SecondsS

Please notice that we can use the letter either to indicate minutes or months depending on whether we use it before or after the 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 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 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 column.

As of today, TypeORM comes with version 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, , converts the interval to a string in the format described at the beginning of this article.

Both other methods, and , work the same in version of the postgres-interval library. They convert the interval to a string in the 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 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
  • dayjs
  • moment.js

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 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 columns in TypeORM and manage the data returned to us by the postgres-interval library.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments