postgresql - How to truncate date in postgres?

Question

I am using below condition to truncate date in postgres

to_date(to_char(trunc(appointment_date),'YYYYMMDD')||appointment_end_time,''YYYYMMDDHH24:MI:SS')AS tq

How I can use this in postgres ?

question from:https://stackoverflow.com/questions/65931242/how-to-truncate-date-in-postgres

Answer 1

As your to_char() call uses the format 'HH24:MI:SS' for the "time" column, you can cast that column directly to a time value, e.g. using the :: operator: appointment_end_time::time.

To build a new timestamp from the date part of the appointment_date and the time value, just add them:

appointment_date::date + appointment_end_time::time

So first the timestamp is converted to a date (that does not have a time), and then the time value is added to that, which yields a timestamp.

---

Note that to_date() returns a date so your code would remove the just added time part again. You would need to use to_timestamp() if you really want a timestamp as the result.

---

To answer the question's title "how to truncate date in Postgres?" (which in reality refers to a timestamp not a date): you can either cast it to a date (see above) or you can use date_trunc() (not trunc()) with a unit to which it should be truncated. However, date_trunc returns a timestamp not a date value, so you couldn't add a time to the result.