Message-ID: <6c2fadf7fb9f5b0f1728b0baf9f112f3204415fe.camel@cybertec.at>
Subject: Re: Convert date and time colums to datetime
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rich Shepard <rshepard@appl-ecosys.com>, pgsql-general@postgresql.org
Date: Sun, 19 Oct 2025 20:09:23 +0200
In-Reply-To: <63dfbc7c-bc63-7fa-a51b-915dd804ea2@appl-ecosys.com>
References: <63dfbc7c-bc63-7fa-a51b-915dd804ea2@appl-ecosys.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/6c2fadf7fb9f5b0f1728b0baf9f112f3204415fe.camel%40cybertec.at>
Precedence: bulk

On Sun, 2025-10-19 at 07:43 -0700, Rich Shepard wrote:
> The database has a table with separate date and time columns.
>=20
> 1. Are there benefits to merging the two into a single timestamp column?

That depends on what you do with the table.
Are your SQL statements simple and natural with the current design?
Then stick with what you have now.
Do you need date arithmetic that is awkward with the current design?
Then a timestamp column is better.

If you design the table from scratch, a timestamp column is almost
always the right thing.  But if the current design works good enough,
you may save yourself the pain of restructuring the table.

For convenience with queries, you could use a view or virtual generated
column, as shown below.

An example for an awkward statement that would strongly indicate that
a timestamp is a better choice:

  UPDATE tab SET
     datecol =3D CAST ((datecol + timecol) + INTERVAL '12 hours' AS date),
     timecol =3D CAST ((datecol + timecol) + INTERVAL '12 hours' AS time)
  WHERE id =3D 42;

> 2. If so, how would I do this? (Reading date/time operators and functions
> doc page hasn't shown me one.)

- If you want to modify the table:

    ALTER tab ADD timestampcol timestamp;

    /* will take a long time if the table is big */
    UPDATE tab SET timestampcol =3D datecol + timecol;

    /* will take a long time if the table is big */
    ALTER TABLE tab ALTER timestampcol SET NOT NULL;

    ALTER TABLE tab DROP datecol,
                    DROP timecol;

    /* to get rid of the bloat from the UPDATE */
    VACUUM (FULL) tab;

- If you just want to use the timestamp in queries, use a view:

    CREATE VIEW v AS
    SELECT *, datecol + timecol AS timestampcol
    FROM tab;

- As an alternative to the previous, you can create a virtual generated
  column in the base table from v18 on:

    ALTER TABLE tab
          ADD timestampcol timestamp GENERATED ALWAYS AS (datecol + timecol=
);

Yours,
Laurenz Albe