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: Thu, 23 Oct 2025 17:23:50 -0600
Message-Id: <600867CF-E73A-4899-9679-209BF69FE329@gmail.com>
References: <rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm>
Cc: pgsql-general@lists.postgresql.org
In-Reply-To: <rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
Archived-At: <https://www.postgresql.org/message-id/600867CF-E73A-4899-9679-209BF69FE329%40gmail.com>
Precedence: bulk


> On Oct 23, 2025, at 1:52=E2=80=AFPM, Peter J. Holzer <hjp-pgsql@hjp.at> wr=
ote:
>=20
> =EF=BB=BFOn 2025-10-20 06:43:17 -0600, Rob Sargent wrote:
>>=20
>>=20
>>>> On Oct 20, 2025, at 5:05=E2=80=AFAM, Peter J. Holzer <hjp-pgsql@hjp.at>=
 wrote:
>>>=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-ecos=
ys.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 t=
hen
>>>>>> an alteration might be in order. There can be a large one time cost i=
f
>>>>>> these tables occur in a lot of separate sql calls in the business log=
ic.
>>>>>=20
>>>>> Good point. They're in the contacts table and I use them to determine w=
hen
>>>>> to make another contact and if prior contacts were more productive in t=
he
>>>>> 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
>> 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 design?
>> Maybe the time column is an interval?
>=20
> The question isn't IMHO whether it *can* be done. Obviously a certain
> point in time can be represented by a timestamp or a date/time pair and
> both will work (as will a whole lot of different representations). The
> question is what feels more "natural" for the given application. Are
> the date and the time often used independently or are they almost
> always used as an atomic entity? My impression from what Rich wrote
> is that it might be the former. Which would suggest also storing them
> independently. Not saying that this is necessarily the right thing to
> do but isn't "definitely a datetime (single value) problem" either.
>=20
>        hjp
>=20
Agreed.=20
My position is that I would have started with timestamp. OP is welcome to ch=
oose what works best for him. Certainly not seriously advocating the alter t=
able and related effort
at this point.=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>