In Postgres, the EPOCH time represents the total number of seconds elapsed from “1st January 1970 00:00:00” till today. The EXTRACT() function is used to calculate the EPOCH time from a specific DateTime value. The EPOCH can be extracted from the current or specific DateTime values.
This write-up presents an in-depth overview of how to extract EPOCH time from DateTime values in Postgres.
How to Calculate EPOCH Time in Postgres?
To extract epoch time from the current or specific time, the EXTRACT() function is used with the EPOCH argument:
EXTRACT (EPOCH FROM DateTime);
Where DateTime can be a “TIMESTAMP”, “DATE”, “TIME”, or “INTERVAL”.
Example 1: Extracting EPOCH FROM a Specific Timestamp
In the following code, a specific timestamp and EPOCH are passed as arguments to get the epoch time:
SELECT EXTRACT (EPOCH FROM TIMESTAMP '2020-01-12 01:10:15');
The EPOCH time has been successfully calculated.
Example 2: Extracting EPOCH FROM the Current Timestamp
Pass the CURRENT_TIMESTAMP function and EPOCH as arguments to the EXTRACT() function to calculate the EPOCH time from the current timestamp:
SELECT EXTRACT (EPOCH FROM CURRENT_TIMESTAMP);
The EPOCH time has been successfully extracted from the current timestamp.
Example 3: Extracting EPOCH FROM a Specific INTERVAL
Specify the EPOCH and a specific INTERVAL as arguments to the EXTRACT() function to get the EPOCH time from a specific INTERVAL:
SELECT EXTRACT(EPOCH FROM INTERVAL '2 days 3 hours ');
The output signifies that the EPOCH seconds have been extracted from the given INTERVAL.
Example 4: Extracting EPOCH FROM a Specific DATE
Pass the EPOCH and a specific DATE as arguments to the EXTRACT() function to fetch the EPOCH time from the given date:
SELECT EXTRACT(EPOCH FROM DATE '2021-01-12 ');
The output snippet shows that the given date has been converted into the EPOCH time.
Example 5: Extracting EPOCH FROM a Current DATE
To extract the EPOCH time from the current date, specify the EPOCH and CURRENT_DATE as arguments to the EXTRACT() function:
SELECT EXTRACT(EPOCH FROM CURRENT_DATE);
The output indicates that the current date has been converted to the EPOCH time.
Example 6: Extracting EPOCH FROM a Specific TIME
To fetch the EPOCH time from the given time, pass the EPOCH and a specific time as arguments to the EXTRACT() function:
SELECT EXTRACT(EPOCH FROM DATE '12:12:12 ');
The output shows that the EPOCH seconds have been fetched from the given TIME.
Example 7: Extracting EPOCH FROM a CURRENT TIME
To get the EPOCH time from the current time, specify the EPOCH and CURRENT_TIME as arguments to the EXTRACT() function:
SELECT EXTRACT(EPOCH FROM CURRENT_TIME);
The above snippet shows the EPOCH time for the current time.
Conclusion
In PostgreSQL, to extract epoch time from the current or specific time, the EXTRACT() function is used with the EPOCH argument. Using the EXTRACT() function a “TIMESTAMP”, “DATE”, “TIME”, or “INTERVAL” can be converted into an EPOCH time. The EPOCH time retrieves the time in seconds and milliseconds. This post presented a thorough guide on how to calculate the EPOCH time in PostgreSQL using the EXTRACT function.
