Date: Thu, 23 Oct 2025 21:52:29 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Convert date and time colums to datetime
Message-ID: <rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <3cq7x5ky2pn2jabsx7bg67b4c34aijnhxc5ewpgmfot73vftd5@pjlqjnh47ymt>
 <9EF7C4D5-613B-42DE-9FBD-1D14B452DA29@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="pllfgwueo3v6omdd"
Content-Disposition: inline
In-Reply-To: <9EF7C4D5-613B-42DE-9FBD-1D14B452DA29@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy%40xsrs2xks2cfm>
Precedence: bulk


--pllfgwueo3v6omdd
Content-Type: text/plain; protected-headers=v1; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Subject: Re: Convert date and time colums to datetime
MIME-Version: 1.0

On 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-eco=
sys.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=
 then
> >>>> 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 lo=
gic.
> >>>=20
> >>> Good point. They're in the contacts table and I use them to determine=
 when
> >>> to make another contact and if prior contacts were more productive in=
 the
> >>> 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?

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.

        hjp

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

--pllfgwueo3v6omdd
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmj6h3YACgkQ8g5IURL+
KF356w//QkuvYNdaV/yFbZQntDXx7xalR/ykTM2ePV/vrXcY7bLXFdZPhaPVgcg3
Wn+Kx1uro2HxnCMjr6I5GQrNZHwC9vaJluaa1/K+L6e/t5QTjQ+8pvmZgg3PQv6Q
HPvZy9zbv49Yi8p6D2Ywsn5djDKypRU6nVHw5GrWXXvCdOdLjweDvZQAlfKp6e1z
M5Q7+ros6+16woRQ+cWB45n3wDdsrWalu/r9CSjrAt5eqWiik3fq6Cg6xoE83jS2
P9zVdMskb45Fh/xcLcOx4/G5dZ46KrGRzvX+53Bn3E528oaSkI1zYpyNuqYAvKC9
sKKH9Dga1d4E7RAELdhPFEbLxStjaN+0uMIK0pPx9QngxuSAUHtxEeFTa0hbo2ve
5FyG1IHg9oKKlE8j4rJJhLEVPSYmJhhaHpJv3cfXb/Zrhaj7+1uPn1I1l+wVFFFa
dy+8E+9QwDZ7B5l7KPFwD6bF1CXlLE6tjnHnAQawVYRxT4D002QtzA9XVZxEkAp9
zLxY42KklshHIEggFkQHEleUI2GsHtdYrvG9n3qkCtS/wlDR16QjPWCGBfJAYNjw
4exrsM6CkzCeJVDNE67iR2NC+6Omo/UnzkESb4l6MQz0s7JhzbjngQz0ylUCH2S0
HxeC3zypJ6YYZRyiaAfdKxmHLcd32bOnnij5ZLZvk0krNDenQ2k=
=1O2e
-----END PGP SIGNATURE-----

--pllfgwueo3v6omdd--