How to Calculate EPOCH Time in PostgreSQL

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');
img

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);
img

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 ');
img

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 ');
img

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);
img

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 ');
img

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);
img

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.