PostgreSQL: How to Get the End of a Day

Often in PostgreSQL you may want to create a timestamp that represents the end of the day for a particular date.

You can use the following basic syntax to do so:

SELECT *,
       start_time::date + 1 - interval '1 sec' AS end_of_day
FROM emp;

This particular example creates a new column named end_of_day that contains a timestamp with the end of the day for the corresponding date in the start_time column.

For example, if the start_time column contains 2022-09-23 04:15:23 then the end_of_day column will contain 2022-09-23 23:59:59.

Here is how this syntax works:

• First, we convert the datetime in the start_time column to a date.
• Then, we add one day.
• Then, we subtract one second to get the last second of the original day.

The following example shows how to use this syntax in practice.

Example: How to Get the End of Day in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees at some company started working on a specific task:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT  PRIMARY KEY,
  start_time TIMESTAMP
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2022-09-23 04:15:23');
INSERT INTO emp VALUES (0002, '2023-04-01 04:05:56');
INSERT INTO emp VALUES (0003, '2024-10-26 07:00:10');
INSERT INTO emp VALUES (0004, '2024-12-14 09:15:00');
INSERT INTO emp VALUES (0005, '2024-12-07 12:56:30');

-- view all rows in table
SELECT * FROM emp;

 empid |     start_time      
-------+---------------------
     1 | 2022-09-23 04:15:23
     2 | 2023-04-01 04:05:56
     3 | 2024-10-26 07:00:10
     4 | 2024-12-14 09:15:00
     5 | 2024-12-07 12:56:30

Suppose that we would like to add a new column that contains a timestamp with the end of the day for each timestamp in the start_time column.

We can use the following syntax to do so:

SELECT *,
       start_time::date + 1 - interval '1 sec' AS end_of_day
FROM emp;

 empid |     start_time      |     end_of_day      
-------+---------------------+---------------------
     1 | 2022-09-23 04:15:23 | 2022-09-23 23:59:59
     2 | 2023-04-01 04:05:56 | 2023-04-01 23:59:59
     3 | 2024-10-26 07:00:10 | 2024-10-26 23:59:59
     4 | 2024-12-14 09:15:00 | 2024-12-14 23:59:59
     5 | 2024-12-07 12:56:30 | 2024-12-07 23:59:59

The resulting table contains a new column named end_of_day that contains a timestamp with the end of the day for the corresponding date in the start_time column.

You can use similar syntax to create a column that contains the end of the day for a given date in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: Calculate Timestamp Difference in Hours
PostgreSQL: Calculate Timestamp Difference in Minutes
PostgreSQL: Calculate Difference Between Dates in Days