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 ?

Bonbon

What data type are appointment_date and appointment_end_time? And what exactly are you trying to do there? Create a timestamp from a date and a time value? – Fontenot

Appointment date is of timestamp without time zone type and appointment _end_time is of type character varying type ...I want to select those fileds – Bonbon

Why are you storing a time value in a varchar column? That is a really bad idea. – Fontenot

Answer 1

Strange data typing, sometimes requires strange, looking at least, queries. Try (see fiddle)

date_trunc('day',appointment_date) 
       + substr(appoinment_end,12)::interval

Hamfurd

Answer 2

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.

Fontenot