Extract Milliseconds from datetime (timestamp) in PostgreSQL

To extract Milliseconds from a datetime i.e. timestamp in PostgreSQL, you can use the DATE_PART() function with the ‘milliseconds’ unit.

Syntax for DATE_PART() Function in Postresql:

unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).

source: The date or timestamp from which to extract the specified part.

In Our case. The values of the unit must be “milliseconds”.

Extract Milliseconds from datetime(timestamp) in postgresql Simple Example:

In order to Extract milliseconds from datetime  in postgresql we will be using DATE_PART() function.

In the below Example we will be passing milliseconds as “unit” argument and datetime as “source” argument in order to extract milliseconds from Date or datetime

 

SELECT DATE_PART('milliseconds', '2024-02-24 15:30:45.123456'::timestamp) AS milliseconds_part;

Output:

Extract Milliseconds from Current date in postgresql :

This query uses the CURRENT_TIMESTAMP function to get the current datetime and then extracts the milliseconds using DATE_PART()

 

SELECT CURRENT_TIMESTAMP AS current_datetime, DATE_PART('milliseconds', CURRENT_TIMESTAMP) AS milliseconds;

Output:

Extract milliseconds from Date in postgresql table:

We will be using  Below Student_detail table for our example to depict on how to extract milliseconds from date in postgresql

Student_detail:

 

In the above table we will be using  DATE_PART() Function, which will take “milliseconds” and column named “birthdaytime” as argument. Which will extract milliseconds from “birthdaytime” column and store in the new column of postgresql table as shown below

 

select *, DATE_PART('milliseconds', birthdaytime::timestamp) AS milliseconds from Student_detail

so the resultant table will have milliseconds column