Subject: Re: Convert date and time colums to datetime

From: Laurenz Albe <[email-masked]>
Date: Sun, 19 Oct 2025 20:09:23 +0200

On Sun, 2025-10-19 at 07:43 -0700, Rich Shepard wrote:
> The database has a table with separate date and time columns.
> 
> 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 = CAST ((datecol + timecol) + INTERVAL '12 hours' AS date),
     timecol = CAST ((datecol + timecol) + INTERVAL '12 hours' AS time)
  WHERE id = 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 = 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

---

From: Rich Shepard <[email-masked]>
Date: Sun, 19 Oct 2025 12:08:19 -0700 (PDT)

> That depends on what you do with the table.

Laurenz,

That makes sense.

> Then stick with what you have now.

That's what I'm going to do. I was curious when a timestamp column was more
efficient, or otherwise preferred, since only a couple of my databases have
a table with both date and time. And neither has many rows, but one could be
quite large some time in the future.

Thanks very much,

Rich