How to use like operator for timestamp in PostgreSQL?

@daisha 

You can use the LIKE operator to match a string pattern in a timestamp column in PostgreSQL. Here is an example:

1
2

SELECT * FROM table_name
WHERE timestamp_column LIKE '2022-12-25%';

This will return all rows from the table_name table where the timestamp_column starts with '2022-12-25'. The % character is used as a wildcard to match any characters after the specified string.

You can also use the ILIKE operator, which is case-insensitive, instead of LIKE if you don't want the search to be case-sensitive.

1
2

SELECT * FROM table_name
WHERE timestamp_column ILIKE '2022-12-25%';

Note that the LIKE and ILIKE operators can only be used with string data types, so if your timestamp column is of a different data type, you will need to cast it to a string before using these operators. For example:

1
2

SELECT * FROM table_name
WHERE CAST(timestamp_column AS TEXT) LIKE '2022-12-25%';

@daisha 

The above query casts the timestamp_column to a text/string data type before applying the LIKE operator.

It is worth mentioning that using the LIKE operator directly on a timestamp column may not be the most efficient way to search for a specific date or range of dates. PostgreSQL provides various date and time functions that can be more suitable for such queries. For example:

1 2 3 4

SELECT * FROM table_name WHERE date_trunc('day', timestamp_column) = '2022-12-25';

In this example, the date_trunc() function is used to truncate the timestamp_column to the day level, and then the equality operator (=) is used to compare it with the specified date '2022-12-25'. This approach avoids unnecessary string conversions and can take advantage of any indexes on the timestamp column for better performance.