API with NestJS #132. Handling date and time in PostgreSQL with Kysely

NestJS SQL

This entry is part 132 of 180 in the API with NestJS

Saving date and time in our database can be tricky, but it’s crucial to do it correctly. In this article, we address this problem using PostgreSQL and Kysely. We also explore time zones and how to handle them when designing our database.

Dates in PostgreSQL

We can determine how our database handles dates by examining the parameter.

By default, is set to ISO, MDY. To learn about other options, take a look at the official documentation.

The result of the query above includes two parts:

  1. the default date and time output,
  2. instructions on how to understand the input.

By default, PostgreSQL represents dates following the ISO 8601 standard, which means the default display format is .

The parameter also tells us that PostgreSQL interprets given dates as month-day-year (MDY).

Because the input format is set to MDY, PostgreSQL treats the first number as the month and the second as the day. Ignoring this can lead to errors when entering our data.

Date columns built into PostgreSQL

There are various column types to pick from when describing the date and time.

DATE

The most straightforward column type we can choose is .

20231105201749_add_created_at_to_articles.ts

This column allows us to store the date without the time. When we retrieve the data from the database, it is an instance of the Date class.

articlesTable.ts

In the dates retrieved from the database, the time is set to .

articles.repository.ts

TIME

If we want to store the time without the date, we can use the column.

20231105201749_add_created_at_to_articles.ts

When using the column, the retrieved data is a string.

articlesTable.ts

We can investigate that in our repository.

articles.repository.ts

TIMESTAMP

Another important date column is the .

20231105201749_add_created_at_to_articles.ts

PostgreSQL stores the timestamp as a numeric value representing a specific moment in time. The way it’s displayed can be influenced by the parameter. Since the default is set to , PostgreSQL displays the date in the ISO format.

When using the column, the retrieved data is an instance of the Date class.

articlesTable.ts

The data that comes from the database includes both the date and the time.

articles.repository.ts

Timezones

The Coordinated Universal Time (UTC) is the primary time standard determined by atomic clocks. Timezones are usually defined by the number of hours ahead or behind UTC.

For instance, Eastern Standard Time (EST) can be expressed as UTC -5. So, if the current UTC is 20:00, the time in New York would be 15:00.

Dealing with time zones can be challenging, as they are linked to geography and politics and can be influenced by daylight saving adjustments. This video offers a great overview of various factors to keep in mind.

PostgreSQL didn’t perform any timezone-related conversions when we used the and columns in this article. This means that when we input a specific date into our database, it will remain the same, regardless of the timezone we later use to display it.

Both of these types have counterparts that take time zones into account. When we use the data type, we can provide the timezone and date. PostgreSQL then converts our input and stores it as UTC.

The official PostgreSQL documentation advises against using the type, which represents time with a timezone. Without date information, it would not be possible to account for daylight-saving time changes.

When we provide a timestamp labeled as Eastern Standard Time, PostgreSQL adds 5 hours before saving it. This ensures that our database remains consistent, and our time stays accurate, even if we input data using different timezones.

Let’s add the column to our articles and use the column type.

20231105221040_add_scheduled_date_to_articles.ts

Inserting the date into the database

To ensure the validity of the data our users provide, we can use the library. Kysely accepts data in either the Date class or as an ISO string. Let’s require the users to provide the ISO strings.

article.dto.ts

The parameter ensures that the given date is valid, taking into account factors such as leap days.

Since we allow the users to provide the date as an ISO string, but retrieve it from the database using the Date class, we should use the generic type.

articlesTable.ts

The next step is to adjust our model.

article.model.ts

We can now use our new property when creating the articles.

articles.repository.ts

Default values

We do not always have to provide the date manually. Instead, let’s adjust our migration that adds the column.

Above, we use the function built into PostgreSQL to provide the default value for the column. Since we provide a default value, we can also make this column non-nullable and don’t worry about the articles we already have in the database.

Summary

In this article, we’ve explored different methods for storing date and time data with PostgreSQL. We’ve also learned how to use Kysely to define various types of date columns in our schema. Besides that, we’ve used the library to validate the dates provided through the API.

Timezones can be the source of various bugs and problems. By utilizing the timestamp with timezone type in PostgreSQL, we can maintain data consistency, regardless of the time zones in which our users provide the data. Thanks to that, we can minimize the chance of timezone-related issues.

Series Navigation<< API with NestJS #131. Unit tests with PostgreSQL and KyselyAPI with NestJS #133. Introducing database normalization with PostgreSQL and Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments