public inbox for [email protected]
help / color / mirror / Atom feedRe: Convert date and time colums to datetime
2+ messages / 2 participants
[nested] [flat]
* Re: Convert date and time colums to datetime
@ 2025-10-19 18:09 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Laurenz Albe @ 2025-10-19 18:09 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Convert date and time colums to datetime
@ 2025-10-19 19:08 Rich Shepard <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Rich Shepard @ 2025-10-19 19:08 UTC (permalink / raw)
To: pgsql-general
On Sun, 19 Oct 2025, Laurenz Albe wrote:
> That depends on what you do with the table.
Laurenz,
That makes sense.
> Are your SQL statements simple and natural with the current design?
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-19 19:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-19 18:09 Re: Convert date and time colums to datetime Laurenz Albe <[email protected]>
2025-10-19 19:08 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox