public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Rich Shepard <[email protected]>
To: [email protected]
Subject: Re: Convert date and time colums to datetime
Date: Sun, 19 Oct 2025 20:09:23 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Convert date and time colums to datetime
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox