PostgreSQL: How to Get the Start of a Day

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

You can use the following basic syntax to do so:

SELECT *,
       (start_time::date)::timestamp AS start_of_day
FROM emp;

This particular example creates a new column named start_of_day that contains a timestamp with the start 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 start_of_day column will contain 2022-09-23 00:00:00.

Here is how this syntax works:

• First, we cast the timestamp in the start_time column to a date. This removes the time portion of the timestamp.
• Then, we cast the this date to a timestamp.
• The end result is a timestamp that contains the start of the day for the original date.

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

Example: How to Get the Start 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 start of the day for each timestamp in the start_time column.

We can use the following syntax to do so:

SELECT *,
       (start_time::date)::timestamp AS start_of_day
FROM emp;

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

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

You can use similar syntax to create a column that contains the start 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