API with NestJS #159. Date and time with PostgreSQL and the Drizzle ORM

JavaScript

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

Storing date and time in a database can be challenging, but it’s important to get it right. In this article, we solve this problem using PostgreSQL and the Drizzle ORM. We also delve into time zones and how to manage them in our database.

Managing dates with PostgreSQL

We can understand how our database handles dates by checking the parameter.

PostgreSQL show datestyle

The default setting for DateStyle is ISO, MDY. For information on other options, check out the official documentation.

The result of the query above has two parts:

  1. the default output of the date and time
  2. instructions on interpreting the input

By default, PostgreSQL uses the ISO 8601 standard to represent dates, which means they are displayed in the  format.

PostgreSQL select now

The  parameter shows that, by default, PostgreSQL interprets dates in the month-day-year (MDY) format.

 

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

Date columns in PostgreSQL

When defining date and time, there are several column types to choose from.

DATE

The simplest column type we can use is .

database-schema.ts

The above column type lets us store the date without the time. When we fetch it from the database, it is a string by default. However, we can change it to an instance of the Date class by changing the parameter.

database-schema.ts

When we retrieve the date from the database in the mode, the time is set to .

TIME

We can use the data type to store the time without the date.

database-schema.ts

TIMESTAMP

Another data type we should mention is the .

database-schema.ts

PostgreSQL stores timestamps as numbers representing specific moments in time. The  parameter can affect their display. With the default set to ISO, MDY, PostgreSQL shows the date in ISO format.

By default, the Drizzle ORM presents the timestamp as an instance of the Date class, but we can provide it with the argument.

Timezones

Coordinated Universal Time (UTC) is the primary time standard defined by atomic clocks. Timezones are typically defined by their time offset compared to UTC. For example, Eastern Standard Time (EST) is UTC -5. Therefore, if the current UTC is 15:00, the time in New York would be 10:00.

Managing time zones can be complex due to their ties to geography and politics and because of the daylight-saving adjustments. This video provides an excellent overview of the various factors to consider.

In this article, PostgreSQL didn’t perform any timezone conversions when we used the and columns. This means that when we enter a specific date into the database, it remains unchanged, no matter what timezone we later use.

Instead of using the data type, we can use , which allows us to include the timezone with the date. PostgreSQL then converts the input and stores it as UTC.

PostgreSQL documentation advises not to use the type that represents the time with a timezone. It’s not possible to take the daylight-saving time changes into account without the date information.

When we provide a timestamp in Eastern Standard Time, PostgreSQL adds 5 hours before storing it. This ensures our database remains consistent and the time stays accurate, even when data is entered from different time zones.

Dates with NestJS

Let’s create the column for the articles in our database using the type.

database-schema.ts

Let’s use the library to ensure our users provide data as a valid ISO string. Since Drizzle ORM requires us to provide dates as instances of the Date class, we need to use the library to transform the user’s input into a string.

create-article.dto.ts

We can now use Drizzle ORM to create an article with a scheduled date, for example.

articles.service.ts

Summary

In this article, we’ve covered different methods for storing the date and time in PostgreSQL databases. We also used the Drizzle ORM to define various date column types in our schema and used the  library to validate dates provided through the API.

Dealing with timezones can be quite a headache. Thanks to using the timestamp with timezone type in PostgreSQL, we can keep our data consistent, no matter which timezones our users are in, minimizing the chances of timezone-related problems.

Series Navigation<< API with NestJS #158. Soft deletes with the Drizzle ORMAPI with NestJS #160. Using views with the Drizzle ORM and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments