Re: Convert date and time colums to datetime

On Sun, 19 Oct 2025 at 12:35, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:

> On Sun, 19 Oct 2025, Adrian Klaver wrote:
>
> > 2) If you really need a timestamp the work is already done, instead of
> > building on the fly.
>
> 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.

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.