Managing date and time with PostgreSQL and TypeORM

March 15, 2021

While dealing with data, we often have to handle the date and the time. When doing so, there are quite a few things to consider. In this article, we approach various issues both from the standpoint of PostgreSQL and TypeORM.

Ways to store and display date and time in PostgreSQL

By default, Postgres represents dates following the ISO 8601 standard. We can verify that by running the following query:

PostgreSQL show datestyle

The DateStyle variable consists of two components:

the default date/time output
the interpretation of the input

Since ISO is the default date and time output, the display format is YYYY-MM-DD. To see that in action, let’s use the NOW() function that returns the current date and time.

PostgreSQL select now

To experience the interpretation of the input, let’s insert a new Post with the scheduledDate column.

PostgreSQL inset into post

Above, since the input is set to MDY (month-day-year), 04 is treated as the month, and the 05 is treated as the day. Using a date that does not apply to this format causes an error.

PostgreSQL inset into post

Using YYYY-MM-DD above would also work fine when the DateStyle is set to MDY.

Although by default DateStyle is set to ISO, MDY, there are a few other possibilities. If you would like to experiment with them, check out the official documentation.

Columns built into Postgres to manage date and time

There are various columns that we could use to describe the date and the time. In the previous paragraph of this article, we could see the output of the SELECT NOW()::DATE query. We use the double colons to cast the return value of the NOW function to the date type.

Aside from the date column, there are a few notable types that we should mention. One of them is the time column type.

PostgreSQL select now

Above, we can see that time is represented in the 24h format. We have hours, minutes, and seconds that include a fractional value.

A significant column that we also need to mention is the timestamp data type. It stores both the date and the time.

PostgreSQL select now timestamp

Under the hood, Postgres stores timestamps as numbers that represent a specific moment in time. The way they are displayed is based on our DateStyle. Since the default setting of the DateStyle is ISO, MDY, Postgres displays the date in the ISO format.

The timezones

Dealing with timezones can be quite troublesome. They depend both on geography and politics and can even vary due to daylight saving changes. There are many different cases to consider, and this video gives an excellent summary of them.

Both the time and timestamp types have their versions that include the timezone. Although SQL allows timezones with the time type, using it might be tricky. Without the information about the date, we are not able to handle the daylight-saving time. The PostgreSQL documentation discourages from using it.

When using the timestamp type in the queries above in this article, Postgres displayed it as without timezone. In this variant, PostgreSQL stores the local date-time and treats it as if we didn’t specify the time zone. When we use the timestamp without a timezone, PostgreSQL does no timezone-related conversion. When we enter 2021-03-15 15:00:00 into our database, it will always stay the same no matter in what timezone we display it later.

The coordinated universal time (UTC) is a primary time standard used across the world. Time zones are usually defined by a difference of hours from the UTC time. An example is Eastern Standard Time (EST) which can be described as UTC -5. If currently, the UTC time would be 15:00, clocks in New York would show 10:00.

The timestamp with timezone stores the data internally as if the date would be in UTC. Aside from that, it also saves the point on the UTC timeline. Thanks to putting those two pieces of information together, Postgres converts the time to match our timezone.

We can see the current timezone configuration by running the following query:

PostgreSQL show time zone

Since our timezone is configured to UTC, saving a timestamp marked as Eastern Standard Time adds 5 hours when displaying the result.

PostgreSQL select timestamptz

Using date columns with TypeORM

First, let’s look into the date and time column types.

@Column({ type: 'time' })

timeOnly: string;

@Column({ type: 'date' })

dateOnly: string;

The Date object in JavaScript includes both the date and the time. Neither the time nor the date columns alone carry the full information required to create a Date object. Because of that, TypeoORM serializes them to strings, even though it seems to cause confusion.

TypeORM works differently with the timestamp and timestamp with timezone columns.

@Column({ type: 'timestamp', nullable: true })

timestamp: Date;

@Column({ type: 'timestamptz', nullable: true })

timestampWithTimezone: Date;

In contrast to the time and date columns, the timestamp data time contains everything needed to create a Date object.

Unfortunately, it looks like TypeORM has some issues with handling the timestamp column. We can either apply the suggested workarounds or use the timezone with timestamp column type instead.

Special date columns

TypeORM has a set of decorators that allow us to access various dates associated with a specific entity.

@CreateDateColumn()

createdDate: Date;

@UpdateDateColumn()

updatedDate: Date;

@DeleteDateColumn()

deletedDate: Date;

We don’t need to write values to the above columns explicitly. It happens under the hood automatically.

Summary.

In this article, we’ve gone through what data types in PostgreSQL can describe the date and time. It also included a brief discussion about timezones and how they affect the way we store dates. We’ve also gone through how to manage various time and date columns with TypeORM.