How to write a postgresql query for getting only the date part of timestamp field, from a table

How开发者_如何转开发 to write a postgresql query for getting only the date part of timestamp field, from a table

select DATE(my_field) from my_table;

You have two basic options, each with a number of equivalent expressions. Assuming a TIMESTAMP field named "ts", you can extract the date part:

• By type cast
  • CAST(ts AS DATE) SQL-compliant syntax
  • ts::DATE Historical pg syntax
  • DATE(ts) Actually a function. Note that this syntax is deprecated, per the link above.
• By date/time function
  • EXTRACT(YEAR FROM ts)
  • DATE_PART('YEAR', ts)

Another option would be to cast your timestamp to a date:

SELECT

CAST('2010-01-01 12:12:12' AS date)

Following way work for me

CAST(to_timestamp(timestamp_value/1000) AS date) as created_date