How to Use Where Clause in the Date Field Using PostgreSQL
This tutorial will teach us how to perform the WHERE query in the date field. There are several formats of the date representation; generally, it returns as a string or varchar.
Sometimes the format error occurs. All you need to do is, use cast() to cast the column as a date type and then use it inside the where clause.
Use the WHERE Clause in the Date Field Using PostgreSQL
Let’s suppose you have a Users table, and it has a field that contains date.
create table Users (
id INT PRIMARY KEY,
full_name VARCHAR(50),
email VARCHAR(50),
register DATE
);
insert into Users (id, full_name, email, register)
values
( 1, 'Trula', 'ttawse0@washingtonpost.com', '2022-06-17'),
( 2, 'Ansel', 'acotter1@friendfeed.com', '2022-06-18'),
( 3, 'Baillie', 'bgloves2@squidoo.com', '2022-06-18'),
( 4, 'Lay', 'lforsythe3@digg.com', '2021-11-23'),
( 5, 'Berton', 'bfowley4@myspace.com', '2021-12-05'),
( 6, 'Malory', 'mflack5@salon.com', '2022-01-31'),
( 7, 'Fernanda', 'fianson6@meetup.com', '2021-11-26'),
( 8, 'Hester', 'hshyram7@uiuc.edu', '2022-03-13'),
( 9, 'Ced', 'cmorsey8@goo.gl', '2021-09-18'),
( 10, 'Tommy', 'tleipoldt9@sbwire.com', '2022-04-05');
select* from Users;
Output:
id | full_name | email | register
----+-----------+----------------------------+------------
1 | Trula | ttawse0@washingtonpost.com | 2022-06-17
2 | Ansel | acotter1@friendfeed.com | 2022-06-18
3 | Baillie | bgloves2@squidoo.com | 2022-06-18
4 | Lay | lforsythe3@digg.com | 2021-11-23
5 | Berton | bfowley4@myspace.com | 2021-12-05
6 | Malory | mflack5@salon.com | 2022-01-31
7 | Fernanda | fianson6@meetup.com | 2021-11-26
8 | Hester | hshyram7@uiuc.edu | 2022-03-13
9 | Ced | cmorsey8@goo.gl | 2021-09-18
10 | Tommy | tleipoldt9@sbwire.com | 2022-04-05
(10 rows)
Let’s say we want to see a user who registered on 17/06/22, but the problem is that it’s not the correct format to search in the date type field.
So we need to convert it to the ISO-8601 format, so the equivalent of the ISO-8601 format will be 2022-06-17. Here’s the query to search:
SELECT *
FROM USERS
WHERE register::date = '2022-06-17';
Output:
id | full_name | email | register
----+-----------+----------------------------+------------
1 | Trula | ttawse0@washingtonpost.com | 2022-06-17
(1 row)
Also, you can try more functions from the date type. For example, the date_trunc function.
SELECT * FROM USERS WHERE date_trunc('day', register) = '2022-06-17';
Output:
id | full_name | email | register
----+-----------+----------------------------+------------
1 | Trula | ttawse0@washingtonpost.com | 2022-06-17
(1 row)
Here is another example query:
SELECT * FROM USERS WHERE date_trunc('month', register) = '2022-06-17';
Output:
id | full_name | email | register
----+-----------+-------+----------
(0 rows)s
To know more about the date in Postgres, visit the official documentation.
