Subject: Re: Convert date and time colums to datetime

From: Adrian Klaver <[email-masked]>
Date: Sun, 19 Oct 2025 07:53:56 -0700

On 10/19/25 07:43, Rich Shepard wrote:
> The database has a table with separate date and time columns.
> 
> 1. Are there benefits to merging the two into a single timestamp column?

1) One less column to fetch from.

2) If you really need a timestamp the work is already done, instead of 
building on the fly.

> 
> 2. If so, how would I do this? (Reading date/time operators and functions
> doc page hasn't shown me one.)

select ('10/19/2025'::date + '07:50'::time)::timestamptz;

timestamptz
------------------------
  2025-10-19 07:50:00-07

> TIA,
> Rich

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 19 Oct 2025 07:56:57 -0700

> select ('10/19/2025'::date + '07:50'::time)::timestamptz;

Should have added in previous post, the above assumes your current date 
and time values are from the server timezone.

---

From: Rich Shepard <[email-masked]>
Date: Sun, 19 Oct 2025 09:35:39 -0700 (PDT)

> 2) If you really need a timestamp the work is already done, instead of 

Adrian,

As each row in the table already has both a date column and a time column I
don't know if I 'really' need a timestamp. When would a timestamp be really
needed?

> select ('10/19/2025'::date + '07:50'::time)::timestamptz;

Yes, I saw that on the doc page. This requires manually changing each row in
the table rather than using a date/time condition/function to to create the
single timestamp column. So, apparently there's not a way to modify the
table other than by hand.

Thanks,

Rich

---

From: Isaac Morland <[email-masked]>
Date: Sun, 19 Oct 2025 12:46:11 -0400

> table other than by hand.

If you're talking about actually changing the table, replacing the two
columns with a single column, you would need ALTER TABLE. Something like
(not tested, just to give you the basic idea):

ALTER TABLE [table]
    ADD [new_column] timestamp;
UPDATE [table]
    SET [new_column] = [date_column] + [time_column];
ALTER TABLE [table]
    DROP date_column,
    DROP time_column;

The answer already given essentially tells you what to put in the UPDATE
statement, which is an important element. The following page may help with
details:

https://www.postgresql.org/docs/current/sql-altertable.html

Of course, all queries that touch the table need to be updated. There are
some new features that might help with migration; for example, if you made
a new timestamp column that is a generated column, you could have both
co-existing in the table at the same time while you update the users of the
table to use the new column. You also might be able to do something with
defaults to allow the column adding to also populate the new column
appropriately, which would allow you to just do a single ALTER TABLE and no
UPDATE.

I personally would almost always combine date+time into a single timestamp.
It's easier for computations, and whenever you need just one all you have
to do is cast to date or time as appropriate.

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 19 Oct 2025 09:59:39 -0700

> don't know if I 'really' need a timestamp. When would a timestamp be really

I would say the fact you are asking is an indication you might need it.

> table other than by hand.

1) Again you seem to be establishing a need for combined value.

2) Not sure how you  do "...date/time condition/function to to create 
the single timestamp column" without creating a column?

If you meant creating a timestamp value then I point you at my original 
post and:

"2) If you really need a timestamp the work is already done, instead of 
building on the fly."

Doing the one time change to add a timestamp field to the table and then 
do the UPDATE of date + time to populate it versus calculating it every 
time you need the value. Of course moving forward you would need to 
change the code that INSERTs new values to use a timestamp instead of a 
date and a time value.

---

From: Rich Shepard <[email-masked]>
Date: Sun, 19 Oct 2025 10:56:34 -0700 (PDT)

> (not tested, just to give you the basic idea):

Issac,

I thought it could be that simple, while I was looking in the date/time
conditions/functions.

Thanks,

Rich