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.
database-schema.ts
import { serial, text, pgTable, interval } from ‘drizzle-orm/pg-core’;
export const food = pgTable(‘food’, { id: serial(‘id’).primaryKey(), name: text(‘name’), expiryAfterOpening: interval(‘expiry_after_opening’), });
export const databaseSchema = { food, }; |
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.
| Years | Y |
| Months / Minutes | M |
| Weeks | W |
| Days | D |
| Hours | H |
| Seconds | S |
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.
IsIsoInterval.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | import { ValidationArguments, ValidatorConstraint, ValidatorConstraintInterface, } from ‘class-validator’; import { Duration } from ‘luxon’;
@ValidatorConstraint() export class IsIsoInterval implements ValidatorConstraintInterface { validate(value: unknown) { if (typeof value !== ‘string’) { return false; } const interval = Duration.fromISO(value); return interval.isValid; } defaultMessage({ property }: ValidationArguments) { return `${property} must be a valid ISO duration`; } } |
We can now use the custom validator in our DTO.
CreateFood.dto.ts
import { IsString, IsNotEmpty, Validate } from ‘class-validator’; import { IsIsoInterval } from ‘./IsIsoInterval’;
export class CreateFoodDto { @IsString() @IsNotEmpty() name: string;
@IsString() @IsNotEmpty() @Validate(IsIsoInterval) expiryAfterOpening: string; } |
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
ALTER 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.
food.service.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | import { Injectable, NotFoundException } from ‘@nestjs/common’; import { DrizzleService } from ‘../database/drizzle.service’; import { databaseSchema } from ‘../database/database-schema’; import { eq } from ‘drizzle-orm’; import { UpdateFoodDto } from ‘./dto/update-food.dto’; import { Duration } from ‘luxon’;
@Injectable() export class FoodService { constructor(private readonly drizzleService: DrizzleService) {}
async decreaseExpirationByOneDay(id: number) { const food = await this.getById(id);
const interval = Duration.fromISO(food.expiryAfterOpening); const newInterval = interval.minus({ days: 1 });
await this.update(id, { expiryAfterOpening: newInterval.toISO(), }); }
async getById(id: number) { const foodResults = await this.drizzleService.db .select() .from(databaseSchema.food) .where(eq(databaseSchema.food.id, id)); const food = foodResults.pop(); if (!food) { throw new NotFoundException(); } return food; }
async update(id: number, food: UpdateFoodDto) { const updatedFoods = await this.drizzleService.db .update(databaseSchema.food) .set({ name: food.name, expiryAfterOpening: food.expiryAfterOpening, }) .where(eq(databaseSchema.food.id, id)) .returning();
if (updatedFoods.length === 0) { throw new NotFoundException(); }
return updatedFoods.pop(); } } |
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.
#API #NestJS #Time #intervals #Drizzle #ORM #PostgreSQL
