MIME-Version: 1.0
References: <63dfbc7c-bc63-7fa-a51b-915dd804ea2@appl-ecosys.com>
 <6b30ebac-6d6d-4c26-be73-de67e208e4e1@aklaver.com> <b15aa3a-f878-cb2-1749-b418de4fabe@appl-ecosys.com>
In-Reply-To: <b15aa3a-f878-cb2-1749-b418de4fabe@appl-ecosys.com>
From: Isaac Morland <isaac.morland@gmail.com>
Date: Sun, 19 Oct 2025 12:46:11 -0400
Message-ID: <CAMsGm5ep5ArRCw3KrJBPD-wspLUQeUJXYNhCoLN-Y0fsGUROSg@mail.gmail.com>
Subject: Re: Convert date and time colums to datetime
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000000f3ee064185b53b"
Archived-At: <https://www.postgresql.org/message-id/CAMsGm5ep5ArRCw3KrJBPD-wspLUQeUJXYNhCoLN-Y0fsGUROSg%40mail.gmail.com>
Precedence: bulk

--00000000000000f3ee064185b53b
Content-Type: text/plain; charset="UTF-8"

On Sun, 19 Oct 2025 at 12:35, Rich Shepard <rshepard@appl-ecosys.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.

--00000000000000f3ee064185b53b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, 19 Oct 2025 at 12:35, Rich Shepar=
d &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-ecosys.com<=
/a>&gt; wrote:<br></div><div class=3D"gmail_quote gmail_quote_container"><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);pad=
ding-left:1ex">On Sun, 19 Oct 2025, Adrian Klaver wrote:<br>
<br>
&gt; 2) If you really need a timestamp the work is already done, instead of=
 <br>
&gt; building on the fly.<br>
<br>
Adrian,<br>
<br>
As each row in the table already has both a date column and a time column I=
<br>
don&#39;t know if I &#39;really&#39; need a timestamp. When would a timesta=
mp be really<br>
needed?<br>
<br>
&gt; select (&#39;10/19/2025&#39;::date + &#39;07:50&#39;::time)::timestamp=
tz;<br>
<br>
Yes, I saw that on the doc page. This requires manually changing each row i=
n<br>
the table rather than using a date/time condition/function to to create the=
<br>
single timestamp column. So, apparently there&#39;s not a way to modify the=
<br>
table other than by hand.</blockquote><div><br></div><div>If you&#39;re tal=
king about actually changing the table, replacing the two columns with a si=
ngle column, you would need ALTER TABLE. Something like (not tested, just t=
o give you the basic idea):</div><div><br></div><div>ALTER TABLE [table]</d=
iv><div>=C2=A0 =C2=A0 ADD [new_column] timestamp;</div><div>UPDATE [table]<=
/div><div>=C2=A0 =C2=A0 SET [new_column] =3D [date_column] + [time_column];=
</div><div>ALTER TABLE [table]</div><div>=C2=A0 =C2=A0 DROP date_column,</d=
iv><div>=C2=A0 =C2=A0 DROP time_column;</div><div><br></div><div>The answer=
 already given essentially tells you what to put in the UPDATE statement, w=
hich is an important element. The following page may help with details:</di=
v><div><br></div><div><a href=3D"https://www.postgresql.org/docs/current/sq=
l-altertable.html">https://www.postgresql.org/docs/current/sql-altertable.h=
tml</a><br></div><div><br></div><div>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 genera=
ted column, you could have both co-existing in the table at the same time w=
hile you update the users of the table to use the new column. You also migh=
t 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 s=
ingle ALTER TABLE and no UPDATE.</div><div><br></div><div>I personally woul=
d almost always combine date+time into a single timestamp. It&#39;s easier =
for computations, and whenever you need just one all you have to do is cast=
 to date or time as appropriate.</div></div></div>

--00000000000000f3ee064185b53b--