How to filter on date ranges in Prisma

When fetching records from your database, it’s very common to want to filter records based on date ranges. Fortunately, the Prisma ORM makes this very easy with filter conditions and operators.

When working with date columns, you’ll most commonly be using gt (greater than), gte (greater than or equal to), lt (less than), and lte (less than or equal to). When used in conjunction, these let you write where clauses that filter records based on any date range.

Filter records after a certain date

If you need to filter records after a certain date, you can use either the gte or gt operator depending on your use case. If you want to include the specified date you should use gte, otherwise you should use gt.

Here’s an example of fetching post records that were created on or after August 1, 2023.

const posts = await prisma.post.findMany({
	where: {
		createdAt: {
			gte: new Date('2023-08-01'), // Earliest date
		},
	},
});

Filter records before a certain date

Similarly, if you want to filter records before a certain date, you can use either the lte or lt operator.

Here’s an example of fetching post records that were created on or before August 31, 2023.

const posts = await prisma.post.findMany({
	where: {
		createdAt: {
			lte: new Date('2023-08-31'), // Latest date
		},
	},
});

Filter records between a date range

Putting this together, if you want to filter records between a start and end date, you can use both gte/gt and lte/lt in conjunction.

Here’s an example of how you would fetch all posts created in the month of August.

const posts = await prisma.post.findMany({
	where: {
		createdAt: {
			gte: new Date('2023-08-01'), // Start of date range
			lte: new Date('2023-08-31'), // End of date range
		},
	},
});

Filtering in Prisma

Now you can use these filter operations to fetch records from your database using Prisma.

If you need to do any other kinds of complex filtering in Prisma, you should check out the official Prisma Client API reference.

UI tools

If you're looking for a UI-based tool to inspect and manage records in your database, you can check out Basedash which automatically generates a collaborative interface on your SQL database. It supports filtering on date ranges and much more.