public inbox for [email protected]  

help / color / mirror / Atom feed

From: Isaac Morland <[email protected]>
To: Rich Shepard <[email protected]>
Cc: [email protected]
Subject: Re: Convert date and time colums to datetime
Date: Sun, 19 Oct 2025 12:46:11 -0400
Message-ID: <CAMsGm5ep5ArRCw3KrJBPD-wspLUQeUJXYNhCoLN-Y0fsGUROSg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On Sun, 19 Oct 2025 at 12:35, Rich Shepard <[email protected]> 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.

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Convert date and time colums to datetime
  In-Reply-To: <CAMsGm5ep5ArRCw3KrJBPD-wspLUQeUJXYNhCoLN-Y0fsGUROSg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox