Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Rob Sargent <robjsargent@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Convert date and time colums to datetime
Date: Mon, 20 Oct 2025 06:43:17 -0600
Message-Id: <9EF7C4D5-613B-42DE-9FBD-1D14B452DA29@gmail.com>
References: <3cq7x5ky2pn2jabsx7bg67b4c34aijnhxc5ewpgmfot73vftd5@pjlqjnh47ymt>
Cc: pgsql-general@lists.postgresql.org
In-Reply-To: <3cq7x5ky2pn2jabsx7bg67b4c34aijnhxc5ewpgmfot73vftd5@pjlqjnh47ymt>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
Archived-At: <https://www.postgresql.org/message-id/9EF7C4D5-613B-42DE-9FBD-1D14B452DA29%40gmail.com>
Precedence: bulk


> On Oct 20, 2025, at 5:05=E2=80=AFAM, Peter J. Holzer <hjp-pgsql@hjp.at> wr=
ote:
>=20
> =EF=BB=BFOn 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
>>>> On Oct 19, 2025, at 2:38=E2=80=AFPM, Rich Shepard <rshepard@appl-ecosys=
.com> wrote:
>>> On Sun, 19 Oct 2025, Rob Sargent wrote:
>>>> I think you have to ask why those values were separated in the first
>>>> place. For instance if they are thought of as a pair in most queries th=
en
>>>> an alteration might be in order. There can be a large one time cost if
>>>> these tables occur in a lot of separate sql calls in the business logic=
.
>>>=20
>>> Good point. They're in the contacts table and I use them to determine wh=
en
>>> to make another contact and if prior contacts were more productive in th=
e
>>> morning or afternoon.
>>=20
>> Definitely a datetime (single value) problem, imho
>=20
> Actually, to me that seems to be one of the few cases where splitting
> them makes sense. I would expect typical updates to be something like
> "sane time, but 6 months later" or "same day, but different time". There
> might also be constraints like "not before 9am". For queries there might
> be stuff like "who do I need to call today", or as Rich already
> mentioned, statistics by time of the day. There are probably relatively
> few queries where you need to treat date and time as a unit.
>=20
>        hjp


I don=E2=80=99t see any mention of the current data types of the two columns=
 currently in play. apologies of I missed that.=20

Which of your example updates cannot be done with timestamp? Perhaps the =E2=
=80=9Cnot before=E2=80=9Dconstraint but can that be done with OP=E2=80=99s d=
esign? Maybe the time column is an interval?


>=20
> --
>   _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"
> <signature.asc>