CODEWITHSUNDEEP
sqlpostgresql
Linto davis
Linto davis

Reputation: 752

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

Upvotes: 44

Views: 53031

Answers (4)

Kamini
Kamini

Reputation: 690

Following way work for me

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

Upvotes: 0

Konrad Garus
Konrad Garus

Reputation: 54005

select DATE(my_field) from my_table;

Upvotes: 75

pilcrow
pilcrow

Reputation: 58524

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)

Upvotes: 29

Frank Heikens
Frank Heikens

Reputation: 126992

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

SELECT

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

Upvotes: 3

Related Questions