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

Question

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

Answer 1

select date(my_field) from my_table;

Answer 2

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

SELECT

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

Answer 3

You two basic options, each with a number of equivalent expressions. Assuming a TIMESTAMP field named "ts":

• 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)