The “UNIX TIMESTAMP” also known as the “Unix Epoch” or “POSIX” represents a DateTime in seconds, elapsed since 00:00:00 UTC, January 1, 1970. Database users may need to retrieve the UNIX timestamp when manipulating the date and time values. In such situations, the EXTRACT() and DATE_PART() functions can be used with the EPOCH argument.
This post illustrates a practical guide to getting the Unix TIMESTAMP in Postgres via the EXTRACT() and DATE_PART() functions.
How to Get the Unix Timestamp in Postgres?
Pass the EPOCH argument to the EXTRACT() function or the DATE_PART() function to get a timestamp in Unix format.
The below syntax explains how to get a Unix timestamp using the EXTRACT() function:
EXTRACT(EPOCH FROM 'timestamp');
The below snippet demonstrates how to get a Unix timestamp using the DATE_PART() function:
DATE_PART('EPOCH', 'timestamp');Let’s put these concepts into practice!
Example 1: How to Get Unix Timestamp From Current Timestamp
In Postgres, the CURRENT_TIMESTAMP function returns the current DateTime. However, passing EPOCH and the CURRENT_TIMESTAMP as arguments to the EXTRACT() function will retrieve the current DateTime as Unix Timestamp:
SELECT CURRENT_TIMESTAMP, EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) As Unix_timestamp;
Alternatively, you can use the DATE_PART() function to get the Unix Timestamp from the current timestamp:
SELECT CURRENT_TIMESTAMP,
DATE_PART('EPOCH', CURRENT_TIMESTAMP) As Unix_timestamp;
Example 2: How to Get Unix Timestamp From a Specific Timestamp
The EXTRACT() function and the DATE_PART() function assist us in getting the Unix timestamp from a specific timestamp:
SELECT DATE_PART('EPOCH', TIMESTAMP '2020-12-11 02:12:16.906072'),
EXTRACT('EPOCH' FROM TIMESTAMP '2020-12-11 02:12:16.906072');
This way, you can get a Unix timestamp from a specific timestamp using the EXTRACT() and DATE_PART() functions.
Example 3: How to Get Unix Timestamp From a Specific TIMESTAMPTZ
Similarly, you can use the EXTRACT() or DATE_PART() functions to get the Unix timestamp from a “timestamp with a time zone”:
SELECT DATE_PART('EPOCH', TIMESTAMP '2020-12-11 02:12:16.906072-08'),
EXTRACT('EPOCH' FROM TIMESTAMP '2020-12-11 02:12:16.906072-08');
Example 4: How to Get Unix Timestamp From Current Date
To get a Unix timestamp from the current date or a specific date, the EXTRACT() or DATE_PART() functions can be used as follows:
SELECT DATE_PART('EPOCH', CURRENT_DATE),
EXTRACT('EPOCH' FROM DATE '2020-12-11');
The Unix timestamp is extracted from the current date using the DATE_PART() function. While the Unix timestamp is extracted from a specific date using the EXTRACT() function.
Example 5: How to Get Unix Timestamp From an Interval
In the following example, the DATE_PART() and EXTRACT() functions are used to get the Unix timestamp from a specific interval:
SELECT DATE_PART('EPOCH', INTERVAL '1 Month 2 Days'),
EXTRACT('EPOCH' FROM INTERVAL '1 Month 2 Days');
That’s all from this post.
Conclusion
To get the Unix Timestamp in PostgreSQL, the EXTRACT() and DATE_PART() functions are used with the EPOCH argument. Using these functions, a user can get a Unix timestamp from a date, interval, or timestamp. To do that, pass the EPOCH as a first argument to the EXTRACT() function or the DATE_PART() function and a date, interval, or timestamp as the second argument to get a timestamp in Unix format. This post explained various methods to return a Unix timestamp in PostgreSQL.
