Often in PostgreSQL you may want to remove milliseconds from a timestamp column.
You can use the date_trunc() function with the following syntax to do so:
SELECT *,
date_trunc('second', start_time) AS no_milliseconds
FROM emp;
This particular example creates a new column named no_milliseconds that removes the milliseconds from the timestamp in the column named start_time.
Note that we use the date_trunc() function with ‘second’ as the unit of time to indicate that the datetime should be truncated at seconds, i.e. everything after the seconds should be removed.
The following example shows how to use this syntax in practice.
Example: How to Remove Milliseconds from Timestamp in PostgreSQL
Suppose that we create the following table named emp that contains information about when various employees started working on some task:
-- create table named 'emp'
CREATE TABLE emp (
empID INT,
start_time TIMESTAMP
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-09-01 04:15:00.455');
INSERT INTO emp VALUES (0002, '2024-09-01 04:05:00.129');
INSERT INTO emp VALUES (0003, '2024-09-01 07:00:00.354');
INSERT INTO emp VALUES (0004, '2024-09-01 09:15:01.033');
INSERT INTO emp VALUES (0005, '2024-09-01 12:56:00.014');
-- view all rows in table
SELECT * FROM emp;
empid | start_time
-------+-------------------------
1 | 2024-09-01 04:15:00.455
2 | 2024-09-01 04:05:00.129
3 | 2024-09-01 07:00:00.354
4 | 2024-09-01 09:15:01.033
5 | 2024-09-01 12:56:00.014
Notice that the timestamps in the start_time column currently contain milliseconds.
We can use the following syntax to create a new column that removes the milliseconds from each timestamp:
SELECT *,
date_trunc('second', start_time) AS no_milliseconds
FROM emp;
empid | start_time | no_milliseconds
-------+-------------------------+---------------------
1 | 2024-09-01 04:15:00.455 | 2024-09-01 04:15:00
2 | 2024-09-01 04:05:00.129 | 2024-09-01 04:05:00
3 | 2024-09-01 07:00:00.354 | 2024-09-01 07:00:00
4 | 2024-09-01 09:15:01.033 | 2024-09-01 09:15:01
5 | 2024-09-01 12:56:00.014 | 2024-09-01 12:56:00
Notice that the new column named no_milliseconds contains the timestamp from the start_time column with the milliseconds removed.
Feel free to use similar syntax to remove the milliseconds from a timestamp column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: Calculate Difference Between Dates in Days
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months
