How to write a postgresql query for getting only the date part of timestamp field, from a table
4 Answers
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 syntaxts::DATE
Historical pg syntaxDATE(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)
-
Thanks for robust answer and deprecation warning! Casting it is.– dooleyoCommented Jun 25, 2013 at 18:14
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