Return the Unix Timestamp in PostgreSQL

In PostgreSQL, we can use the extract() function along with the epoch argument to return the Unix timestamp.

We can return the Unix timestamp based on the current date/time, or we can get it based on another specified date/time.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

Get the Current Unix Timestamp

Here’s an example of getting the Unix timestamp from the current date and time:

SELECT extract(epoch from now());

Result:

1650152298.430101

Date Values

Here’s an example of getting the Unix timestamp from a specified date value:

SELECT extract(epoch from date '2030-08-15');

Result:

1912982400

When used with date values, epoch returns the nominal number of seconds since 1970-01-01 00:00:00, without regard to timezone or daylight-savings rules.

Timestamp Values

Here’s an example of getting the Unix timestamp from a specified timestamp value:

SELECT extract(epoch from timestamp '2030-08-15 03:30:45');

Result:

1912995045

When used with timestamp values, epoch returns the nominal number of seconds since 1970-01-01 00:00:00, without regard to timezone or daylight-savings rules. This is the same as when using date values.

Timestamp With Time Zone Value

Here’s an example of getting the Unix timestamp from a specified timestamp with time zone value:

SELECT extract(
    epoch from timestamp with time zone '2030-08-15 03:30:45.12-08'
    );

Result:

1913023845.12

When used with timestamp with time zone values, epoch returns the number of seconds since 1970-01-01 00:00:00 UTC (negative for timestamps before that)

Intervals

We can also get a Unix timestamp from an interval value:

SELECT extract(epoch from interval '7 days 2 hours');

Result:

When used with interval values, epoch returns the total number of seconds in the interval.