public inbox for [email protected]
help / color / mirror / Atom feedRe: Convert date and time colums to datetime
6+ messages / 3 participants
[nested] [flat]
* Re: Convert date and time colums to datetime
@ 2025-10-19 14:53 Adrian Klaver <[email protected]>
2025-10-19 14:56 ` Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-10-19 14:53 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
On 10/19/25 07:43, 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?
1) One less column to fetch from.
2) If you really need a timestamp the work is already done, instead of
building on the fly.
>
> 2. If so, how would I do this? (Reading date/time operators and functions
> doc page hasn't shown me one.)
select ('10/19/2025'::date + '07:50'::time)::timestamptz;
timestamptz
------------------------
2025-10-19 07:50:00-07
>
> TIA,
>
> Rich
>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
@ 2025-10-19 14:56 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-10-19 14:56 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
On 10/19/25 07:53, Adrian Klaver wrote:
> On 10/19/25 07:43, 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?
>
> 1) One less column to fetch from.
>
> 2) If you really need a timestamp the work is already done, instead of
> building on the fly.
>
>>
>> 2. If so, how would I do this? (Reading date/time operators and functions
>> doc page hasn't shown me one.)
>
> select ('10/19/2025'::date + '07:50'::time)::timestamptz;
>
> timestamptz
> ------------------------
> 2025-10-19 07:50:00-07
Should have added in previous post, the above assumes your current date
and time values are from the server timezone.
>
>>
>> TIA,
>>
>> Rich
>>
>>
>>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
@ 2025-10-19 16:35 ` Rich Shepard <[email protected]>
2025-10-19 16:46 ` Re: Convert date and time colums to datetime Isaac Morland <[email protected]>
2025-10-19 16:59 ` Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
1 sibling, 2 replies; 6+ messages in thread
From: Rich Shepard @ 2025-10-19 16:35 UTC (permalink / raw)
To: pgsql-general
On Sun, 19 Oct 2025, Adrian Klaver wrote:
> 2) If you really need a timestamp the work is already done, instead of
> building on the fly.
Adrian,
As each row in the table already has both a date column and a time column I
don't know if I 'really' need a timestamp. When would a timestamp be really
needed?
> select ('10/19/2025'::date + '07:50'::time)::timestamptz;
Yes, I saw that on the doc page. This requires manually changing each row in
the table rather than using a date/time condition/function to to create the
single timestamp column. So, apparently there's not a way to modify the
table other than by hand.
Thanks,
Rich
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <[email protected]>
@ 2025-10-19 16:46 ` Isaac Morland <[email protected]>
2025-10-19 17:56 ` Re: Convert date and time colums to datetime Rich Shepard <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Isaac Morland @ 2025-10-19 16:46 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Sun, 19 Oct 2025 at 12:35, Rich Shepard <[email protected]> wrote:
> On Sun, 19 Oct 2025, Adrian Klaver wrote:
>
> > 2) If you really need a timestamp the work is already done, instead of
> > building on the fly.
>
> Adrian,
>
> As each row in the table already has both a date column and a time column I
> don't know if I 'really' need a timestamp. When would a timestamp be really
> needed?
>
> > select ('10/19/2025'::date + '07:50'::time)::timestamptz;
>
> Yes, I saw that on the doc page. This requires manually changing each row
> in
> the table rather than using a date/time condition/function to to create the
> single timestamp column. So, apparently there's not a way to modify the
> table other than by hand.
If you're talking about actually changing the table, replacing the two
columns with a single column, you would need ALTER TABLE. Something like
(not tested, just to give you the basic idea):
ALTER TABLE [table]
ADD [new_column] timestamp;
UPDATE [table]
SET [new_column] = [date_column] + [time_column];
ALTER TABLE [table]
DROP date_column,
DROP time_column;
The answer already given essentially tells you what to put in the UPDATE
statement, which is an important element. The following page may help with
details:
https://www.postgresql.org/docs/current/sql-altertable.html
Of course, all queries that touch the table need to be updated. There are
some new features that might help with migration; for example, if you made
a new timestamp column that is a generated column, you could have both
co-existing in the table at the same time while you update the users of the
table to use the new column. You also might be able to do something with
defaults to allow the column adding to also populate the new column
appropriately, which would allow you to just do a single ALTER TABLE and no
UPDATE.
I personally would almost always combine date+time into a single timestamp.
It's easier for computations, and whenever you need just one all you have
to do is cast to date or time as appropriate.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <[email protected]>
2025-10-19 16:46 ` Re: Convert date and time colums to datetime Isaac Morland <[email protected]>
@ 2025-10-19 17:56 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Rich Shepard @ 2025-10-19 17:56 UTC (permalink / raw)
To: pgsql-general
On Sun, 19 Oct 2025, Isaac Morland wrote:
> If you're talking about actually changing the table, replacing the two
> columns with a single column, you would need ALTER TABLE. Something like
> (not tested, just to give you the basic idea):
Issac,
I thought it could be that simple, while I was looking in the date/time
conditions/functions.
Thanks,
Rich
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <[email protected]>
@ 2025-10-19 16:59 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-10-19 16:59 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-general
On 10/19/25 09:35, Rich Shepard wrote:
> On Sun, 19 Oct 2025, Adrian Klaver wrote:
>
>> 2) If you really need a timestamp the work is already done, instead of
>> building on the fly.
>
> Adrian,
>
> As each row in the table already has both a date column and a time column I
> don't know if I 'really' need a timestamp. When would a timestamp be really
> needed?
I would say the fact you are asking is an indication you might need it.
>
>> select ('10/19/2025'::date + '07:50'::time)::timestamptz;
>
> Yes, I saw that on the doc page. This requires manually changing each
> row in
> the table rather than using a date/time condition/function to to create the
> single timestamp column. So, apparently there's not a way to modify the
> table other than by hand.
1) Again you seem to be establishing a need for combined value.
2) Not sure how you do "...date/time condition/function to to create
the single timestamp column" without creating a column?
If you meant creating a timestamp value then I point you at my original
post and:
"2) If you really need a timestamp the work is already done, instead of
building on the fly."
Doing the one time change to add a timestamp field to the table and then
do the UPDATE of date + time to populate it versus calculating it every
time you need the value. Of course moving forward you would need to
change the code that INSERTs new values to use a timestamp instead of a
date and a time value.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-10-19 17:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-19 14:53 Re: Convert date and time colums to datetime Adrian Klaver <[email protected]>
2025-10-19 14:56 ` Adrian Klaver <[email protected]>
2025-10-19 16:35 ` Rich Shepard <[email protected]>
2025-10-19 16:46 ` Isaac Morland <[email protected]>
2025-10-19 17:56 ` Rich Shepard <[email protected]>
2025-10-19 16:59 ` Adrian Klaver <[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