Search code examples
databasepostgresqlextract

PostgreSQL error when using EXTRACT to get a unit

I have been trying to extract the month from a specified time stamp:

SELECT EXTRACT(MONTH FROM '2019-01-02 00:00:00+00');

But I get the following error:

ERROR: function pg_catalog.date_part(unknown, unknown) is not unique LINE 1: SELECT EXTRACT(MONTH FROM '2019-01-02 00:00:00+00'); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. SQL state: 42725 Character: 8

However when I use the same command specifying the CURRENT_TIMESTAMP variable, the expected result IS returned.

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);

How can I get the unit from my first attempt to return teh expected result?

Solution

  • Convert the string to timestamp:

    SELECT EXTRACT(MONTH FROM TIMESTAMP '2019-01-02 00:00:00+00');

    A fragment from the documentation:

    EXTRACT(field FROM source)

    (...) source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.)