Sometimes, in our application, we would like to define a specific duration of time. We could represent it as a number of seconds, for example. However, we might want to be more flexible and be able to use various units, such as minutes, hours, days, or weeks. In this article, we learn how to achieve that with PostgreSQL and the Drizzle ORM using intervals.
Defining an interval
Fortunately, the Drizzle ORM supports the interval column type built into PostgreSQL. Let’s use it.
1import { serial, text, pgTable, interval } from 'drizzle-orm/pg-core';
2
3export const food = pgTable('food', {
4 id: serial('id').primaryKey(),
5 name: text('name'),
6 expiryAfterOpening: interval('expiry_after_opening'),
7});
8
9export const databaseSchema = {
10 food,
11};Validating input data
We must ensure users use the correct data format before letting them add rows to our new table. ISO 8601 is a popular format for dates and time-related data. An interval defined with ISO 8601 starts with the letter P followed by the interval value. The letter T separates the date from the time.
An example of a valid ISO interval is P2Y3M4DT5H6M7S.
We can use the M letter either to indicate minutes or months depending on whether we use it before or after T.
The table above shows that P2Y3M4DT5H6M7S means 2 years, 3 months, 4 days, 5 hours, 6 minutes, and 7 seconds.
Let’s validate the data users send through our API before putting it into our database. Unfortunately, JavaScript does not support ISO intervals out of the box. We can use a date library such as Luxon to create a custom validator with the class-validator library to address this.
1import {
2 ValidationArguments,
3 ValidatorConstraint,
4 ValidatorConstraintInterface,
5} from 'class-validator';
6import { Duration } from 'luxon';
7
8@ValidatorConstraint()
9export class IsIsoInterval implements ValidatorConstraintInterface {
10 validate(value: unknown) {
11 if (typeof value !== 'string') {
12 return false;
13 }
14 const interval = Duration.fromISO(value);
15 return interval.isValid;
16 }
17 defaultMessage({ property }: ValidationArguments) {
18 return `${property} must be a valid ISO duration`;
19 }
20}We can now use the custom validator in our DTO.
1import { IsString, IsNotEmpty, Validate } from 'class-validator';
2import { IsIsoInterval } from './IsIsoInterval';
3
4export class CreateFoodDto {
5 @IsString()
6 @IsNotEmpty()
7 name: string;
8
9 @IsString()
10 @IsNotEmpty()
11 @Validate(IsIsoInterval)
12 expiryAfterOpening: string;
13}This approach allows us to respond with the 400 Bad Request status if the user provides an invalid interval.
Interval formats built into PostgreSQL
Let’s make an HTTP request and create a row in our table.
postgres
The database returned the interval in a format different than ISO. By default, PostgreSQL represents the intervals using a format called postgres. We can check that out by looking at the IntervalStyle value.
The postgres format explicitly specifies years, months, and days followed by the time in the hh:mm:ss format.
We use ::INTERVAL to convert a string to an interval.
We can go a step further and specify microseconds, milliseconds, weeks, decades, centuries, or even millennia. Moreover, we can use the - sign to negate a part of the value.
Alternatively, we can use the ago keyword to negate all parts of the date to achieve a negative interval.
postgres_verbose
Another format is postgres_verbose, where hh:mm:ss is replaced with hours, minutes, and seconds stated explicitly. To change IntervalStyle to postgres_verbose in a particular session, we need to run the SET IntervalStyle = 'postgres_verbose' command in our PostgreSQL database.
In postgresql_verbose, the same rules apply when dealing with negative intervals as with the postgres format.
sql_standard
Alternatively, PostgreSQL also supports the sql_standard interval output format. When we use it, PostgreSQL outputs the intervals using the SQL standard.
It starts with the years and months separated by a dash, followed by the number of days and the time separated by spaces.
To create a negative interval, we must use the - sign instead of + next to every section we want to negate.
iso_8601
Fortunately, we can also use the ISO 8601 format, which we explained at the beginning of our article. To change the interval format in our database permanently for all database connections, we need to run the following command
1ALTER DATABASE nestjs SET IntervalStyle = 'iso_8601';where nestjs is the name of our database. Once we do that, PostgreSQL starts using the ISO format for intervals.
To define a negative interval, we must use the - sign before every part of our interval that we want to negate.
Working with intervals
Thanks to changing the interval format in our database to iso_8601 permanently, it also affects our Drizzle ORM queries.
While Drizzle ORM queries the interval as a simple string, we can use Luxon to parse it and interact with it.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5import { UpdateFoodDto } from './dto/update-food.dto';
6import { Duration } from 'luxon';
7
8@Injectable()
9export class FoodService {
10 constructor(private readonly drizzleService: DrizzleService) {}
11
12 async decreaseExpirationByOneDay(id: number) {
13 const food = await this.getById(id);
14
15 const interval = Duration.fromISO(food.expiryAfterOpening);
16 const newInterval = interval.minus({ days: 1 });
17
18 await this.update(id, {
19 expiryAfterOpening: newInterval.toISO(),
20 });
21 }
22
23 async getById(id: number) {
24 const foodResults = await this.drizzleService.db
25 .select()
26 .from(databaseSchema.food)
27 .where(eq(databaseSchema.food.id, id));
28 const food = foodResults.pop();
29 if (!food) {
30 throw new NotFoundException();
31 }
32 return food;
33 }
34
35 async update(id: number, food: UpdateFoodDto) {
36 const updatedFoods = await this.drizzleService.db
37 .update(databaseSchema.food)
38 .set({
39 name: food.name,
40 expiryAfterOpening: food.expiryAfterOpening,
41 })
42 .where(eq(databaseSchema.food.id, id))
43 .returning();
44
45 if (updatedFoods.length === 0) {
46 throw new NotFoundException();
47 }
48
49 return updatedFoods.pop();
50 }
51}For example, in the decreaseExpirationByOneDay method, we decrease the expiration interval by one day. To do that, we used the minus method built into Luxon. If you want to know more about how you can interact with intervals with Luxon, check out the official documentation.
Functions and operators in PostgreSQL
PostgreSQL allows us to perform various operations with intervals. For example, we can subtract them from dates.
We can also add and subtract intervals from each other.
Additionally, we can use math to multiply and divide the intervals.
Summary
In this article, we explored the interval data type in PostgreSQL and learned how to use it with Drizzle ORM. To do that, we had to get familiar with various interval formats built into PostgreSQL. Additionally, we used the Luxon library to implement validation in our API and to interact with the intervals in our TypeScript code. Thanks to combining the intervals functionality built into PostgreSQL with Luxon’s date and time handling, we were able to manage the interval data effectively.