I have syntax error in following query which retrieve month from last_updated_time column from BIZ_TRANSACTION where last_updated_time stored as millisecond. So, how can I apply date_part() function?
I used the following query but it shows syntax error near date_part().
SELECT date_part('month', timestamp TO_CHAR(TO_TIMESTAMP(LAST_UPDATED_TIME / 1000), 'DD/MM/YYYY HH24:MI:SS'))
FROM BIZ_TRANSACTION
If I understand your question correctly, you want something like that:
SELECT date_part('month',to_timestamp(LAST_UPDATED_TIME/1000));
FROM BIZ_TRANSACTION
DATE_PART function requries two arguments of type text and some type of time/date/interval respectively.
When you convert your timestamp to a char type there is no function which matches your input types as it becomes text and text.
Also, this is a superfluous overhead to cast the timestamp to char when you retrieve the month number from it. Formatting doesn't make any difference in that case and it is better to work on Postgres default datestyles so that you won't encounter errors you have just encountered :-)
select date_part('month', to_timestamp(last_updated_time/1000))
from biz_transaction
List of date_part functions for 9.5 version is (see for yourself in psql by typing \df date_part):
Schema | Name | Result datatype | Datatype of arguments
------------+-----------+-------------------+-----------------------------------
pg_catalog | date_part | double precision | text, abstime
pg_catalog | date_part | double precision | text, date
pg_catalog | date_part | double precision | text, interval
pg_catalog | date_part | double precision | text, reltime
pg_catalog | date_part | double precision | text, time with time zone
pg_catalog | date_part | double precision | text, time without time zone
pg_catalog | date_part | double precision | text, timestamp with time zone
pg_catalog | date_part | double precision | text, timestamp without time zone
