pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedConvert date and time colums to datetime
22+ messages / 7 participants
[nested] [flat]
* Convert date and time colums to datetime
@ 2025-10-19 14:43 Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 22+ messages in thread
From: Rich Shepard @ 2025-10-19 14:43 UTC (permalink / raw)
To: pgsql-general
The database has a table with separate date and time columns.
1. Are there benefits to merging the two into a single timestamp column?
2. If so, how would I do this? (Reading date/time operators and functions
doc page hasn't shown me one.)
TIA,
Rich
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-19 14:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 14:56 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 2 replies; 22+ messages in thread
From: Adrian Klaver @ 2025-10-19 14:53 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; 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
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-10-19 14:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 22+ messages in thread
From: Adrian Klaver @ 2025-10-19 14:56 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; 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
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-10-19 16:35 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 16:46 ` Re: Convert date and time colums to datetime Isaac Morland <isaac.morland@gmail.com>
2025-10-19 16:59 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 2 replies; 22+ 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] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-19 16:46 ` Isaac Morland <isaac.morland@gmail.com>
2025-10-19 17:56 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 1 reply; 22+ messages in thread
From: Isaac Morland @ 2025-10-19 16:46 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
On Sun, 19 Oct 2025 at 12:35, Rich Shepard <rshepard@appl-ecosys.com> 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] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 16:46 ` Re: Convert date and time colums to datetime Isaac Morland <isaac.morland@gmail.com>
@ 2025-10-19 17:56 ` Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 0 replies; 22+ 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] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Re: Convert date and time colums to datetime Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 16:35 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-19 16:59 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 22+ messages in thread
From: Adrian Klaver @ 2025-10-19 16:59 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; 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
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-19 18:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 1 reply; 22+ messages in thread
From: Laurenz Albe @ 2025-10-19 18:09 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; 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] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-10-19 19:08 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 22+ 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] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-19 19:40 ` Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rob Sargent @ 2025-10-19 19:40 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
> On Oct 19, 2025, at 1:08 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> 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
>
I think you have to ask why those values were separated in the first place. For instance if they are thought of as a pair in most queries then an alteration might be in order. There can be a large one time cost if these tables occur in a lot of separate sql calls in the business logic.
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-19 20:38 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rich Shepard @ 2025-10-19 20:38 UTC (permalink / raw)
To: pgsql-general
On Sun, 19 Oct 2025, Rob Sargent wrote:
> I think you have to ask why those values were separated in the first
> place. For instance if they are thought of as a pair in most queries then
> an alteration might be in order. There can be a large one time cost if
> these tables occur in a lot of separate sql calls in the business logic.
Rob,
Good point. They're in the contacts table and I use them to determine when
to make another contact and if prior contacts were more productive in the
morning or afternoon.
Thanks,
Rich
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-20 02:32 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:45 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 2 replies; 22+ messages in thread
From: Rob Sargent @ 2025-10-20 02:32 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
> On Oct 19, 2025, at 2:38 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Sun, 19 Oct 2025, Rob Sargent wrote:
>
>> I think you have to ask why those values were separated in the first
>> place. For instance if they are thought of as a pair in most queries then
>> an alteration might be in order. There can be a large one time cost if
>> these tables occur in a lot of separate sql calls in the business logic.
>
> Rob,
>
> Good point. They're in the contacts table and I use them to determine when
> to make another contact and if prior contacts were more productive in the
> morning or afternoon.
>
> Thanks,
>
> Rich
>
>
Definitely a datetime (single value) problem, imho
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-20 11:05 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:43 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Peter J. Holzer @ 2025-10-20 11:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
> > On Oct 19, 2025, at 2:38 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> > On Sun, 19 Oct 2025, Rob Sargent wrote:
> >> I think you have to ask why those values were separated in the first
> >> place. For instance if they are thought of as a pair in most queries then
> >> an alteration might be in order. There can be a large one time cost if
> >> these tables occur in a lot of separate sql calls in the business logic.
> >
> > Good point. They're in the contacts table and I use them to determine when
> > to make another contact and if prior contacts were more productive in the
> > morning or afternoon.
>
> Definitely a datetime (single value) problem, imho
Actually, to me that seems to be one of the few cases where splitting
them makes sense. I would expect typical updates to be something like
"sane time, but 6 months later" or "same day, but different time". There
might also be constraints like "not before 9am". For queries there might
be stuff like "who do I need to call today", or as Rich already
mentioned, statistics by time of the day. There are probably relatively
few queries where you need to treat date and time as a unit.
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../../3cq7x5ky2pn2jabsx7bg67b4c34aijnhxc5ewpgmfot73vftd5@pjlqjnh47ymt/2-signature.asc)
download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2025-10-20 12:43 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:51 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-23 19:52 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 2 replies; 22+ messages in thread
From: Rob Sargent @ 2025-10-20 12:43 UTC (permalink / raw)
To: Peter J. Holzer <hjp-pgsql@hjp.at>; +Cc: pgsql-general@lists.postgresql.org
> On Oct 20, 2025, at 5:05 AM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
>>>> On Oct 19, 2025, at 2:38 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>>> On Sun, 19 Oct 2025, Rob Sargent wrote:
>>>> I think you have to ask why those values were separated in the first
>>>> place. For instance if they are thought of as a pair in most queries then
>>>> an alteration might be in order. There can be a large one time cost if
>>>> these tables occur in a lot of separate sql calls in the business logic.
>>>
>>> Good point. They're in the contacts table and I use them to determine when
>>> to make another contact and if prior contacts were more productive in the
>>> morning or afternoon.
>>
>> Definitely a datetime (single value) problem, imho
>
> Actually, to me that seems to be one of the few cases where splitting
> them makes sense. I would expect typical updates to be something like
> "sane time, but 6 months later" or "same day, but different time". There
> might also be constraints like "not before 9am". For queries there might
> be stuff like "who do I need to call today", or as Rich already
> mentioned, statistics by time of the day. There are probably relatively
> few queries where you need to treat date and time as a unit.
>
> hjp
I don’t see any mention of the current data types of the two columns currently in play. apologies of I missed that.
Which of your example updates cannot be done with timestamp? Perhaps the “not before”constraint but can that be done with OP’s design? Maybe the time column is an interval?
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp@hjp.at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
> <signature.asc>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:43 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-20 12:51 ` Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 0 replies; 22+ messages in thread
From: Rich Shepard @ 2025-10-20 12:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Mon, 20 Oct 2025, Rob Sargent wrote:
> I don’t see any mention of the current data types of the two columns
> currently in play. apologies of I missed that.
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+-----------------
contact_date | date | | not null | CURRENT_DATE
contact_time | time without time zone | | |
Rich
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:43 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-23 19:52 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-23 23:23 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Peter J. Holzer @ 2025-10-23 19:52 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2025-10-20 06:43:17 -0600, Rob Sargent wrote:
>
>
> > On Oct 20, 2025, at 5:05 AM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >
> > On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
> >>>> On Oct 19, 2025, at 2:38 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> >>> On Sun, 19 Oct 2025, Rob Sargent wrote:
> >>>> I think you have to ask why those values were separated in the first
> >>>> place. For instance if they are thought of as a pair in most queries then
> >>>> an alteration might be in order. There can be a large one time cost if
> >>>> these tables occur in a lot of separate sql calls in the business logic.
> >>>
> >>> Good point. They're in the contacts table and I use them to determine when
> >>> to make another contact and if prior contacts were more productive in the
> >>> morning or afternoon.
> >>
> >> Definitely a datetime (single value) problem, imho
> >
> > Actually, to me that seems to be one of the few cases where splitting
> > them makes sense. I would expect typical updates to be something like
> > "sane time, but 6 months later" or "same day, but different time". There
> > might also be constraints like "not before 9am". For queries there might
> > be stuff like "who do I need to call today", or as Rich already
> > mentioned, statistics by time of the day. There are probably relatively
> > few queries where you need to treat date and time as a unit.
>
> Which of your example updates cannot be done with timestamp? Perhaps
> the “not before”constraint but can that be done with OP’s design?
> Maybe the time column is an interval?
The question isn't IMHO whether it *can* be done. Obviously a certain
point in time can be represented by a timestamp or a date/time pair and
both will work (as will a whole lot of different representations). The
question is what feels more "natural" for the given application. Are
the date and the time often used independently or are they almost
always used as an atomic entity? My impression from what Rich wrote
is that it might be the former. Which would suggest also storing them
independently. Not saying that this is necessarily the right thing to
do but isn't "definitely a datetime (single value) problem" either.
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../../rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm/2-signature.asc)
download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:43 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-23 19:52 ` Re: Convert date and time colums to datetime Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2025-10-23 23:23 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Rob Sargent @ 2025-10-23 23:23 UTC (permalink / raw)
To: Peter J. Holzer <hjp-pgsql@hjp.at>; +Cc: pgsql-general@lists.postgresql.org
> On Oct 23, 2025, at 1:52 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2025-10-20 06:43:17 -0600, Rob Sargent wrote:
>>
>>
>>>> On Oct 20, 2025, at 5:05 AM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>>>
>>> On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
>>>>>> On Oct 19, 2025, at 2:38 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>>>>> On Sun, 19 Oct 2025, Rob Sargent wrote:
>>>>>> I think you have to ask why those values were separated in the first
>>>>>> place. For instance if they are thought of as a pair in most queries then
>>>>>> an alteration might be in order. There can be a large one time cost if
>>>>>> these tables occur in a lot of separate sql calls in the business logic.
>>>>>
>>>>> Good point. They're in the contacts table and I use them to determine when
>>>>> to make another contact and if prior contacts were more productive in the
>>>>> morning or afternoon.
>>>>
>>>> Definitely a datetime (single value) problem, imho
>>>
>>> Actually, to me that seems to be one of the few cases where splitting
>>> them makes sense. I would expect typical updates to be something like
>>> "sane time, but 6 months later" or "same day, but different time". There
>>> might also be constraints like "not before 9am". For queries there might
>>> be stuff like "who do I need to call today", or as Rich already
>>> mentioned, statistics by time of the day. There are probably relatively
>>> few queries where you need to treat date and time as a unit.
>>
>> Which of your example updates cannot be done with timestamp? Perhaps
>> the “not before”constraint but can that be done with OP’s design?
>> Maybe the time column is an interval?
>
> The question isn't IMHO whether it *can* be done. Obviously a certain
> point in time can be represented by a timestamp or a date/time pair and
> both will work (as will a whole lot of different representations). The
> question is what feels more "natural" for the given application. Are
> the date and the time often used independently or are they almost
> always used as an atomic entity? My impression from what Rich wrote
> is that it might be the former. Which would suggest also storing them
> independently. Not saying that this is necessarily the right thing to
> do but isn't "definitely a datetime (single value) problem" either.
>
> hjp
>
Agreed.
My position is that I would have started with timestamp. OP is welcome to choose what works best for him. Certainly not seriously advocating the alter table and related effort
at this point.
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp@hjp.at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
> <signature.asc>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-20 12:45 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 12:53 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Rich Shepard @ 2025-10-20 12:45 UTC (permalink / raw)
To: pgsql-general
On Sun, 19 Oct 2025, Rob Sargent wrote:
> Definitely a datetime (single value) problem, imho
Rob,
Okay. Now I'm curious: why do you write this?
Thanks,
Rich
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:45 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-20 12:53 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:58 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rob Sargent @ 2025-10-20 12:53 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
> On Oct 20, 2025, at 6:45 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Sun, 19 Oct 2025, Rob Sargent wrote:
>
>> Definitely a datetime (single value) problem, imho
>
> Rob,
>
> Okay. Now I'm curious: why do you write this?
>
> Thanks,
>
> Rich
>
The way I read your description of how you use these columns currently suggests to me that they could be handled by a single timestamp column. The cost/benefit of converting is another thing altogether.
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:45 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 12:53 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
@ 2025-10-20 12:58 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 13:00 ` Re: Convert date and time colums to datetime Ron Johnson <ronljohnsonjr@gmail.com>
2025-10-20 13:26 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Rich Shepard @ 2025-10-20 12:58 UTC (permalink / raw)
To: pgsql-general
On Mon, 20 Oct 2025, Rob Sargent wrote:
>> Okay. Now I'm curious: why do you write this?
> The way I read your description of how you use these columns currently
> suggests to me that they could be handled by a single timestamp column.
> The cost/benefit of converting is another thing altogether.
Rob,
Since either way works I'll save the time cost of converting because my
tables will never be really large.
Thanks,
Rich
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:45 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 12:53 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:58 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-20 13:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Ron Johnson @ 2025-10-20 13:00 UTC (permalink / raw)
To: pgsql-general
On Mon, Oct 20, 2025 at 8:58 AM Rich Shepard <rshepard@appl-ecosys.com>
wrote:
> On Mon, 20 Oct 2025, Rob Sargent wrote:
>
> >> Okay. Now I'm curious: why do you write this?
>
> > The way I read your description of how you use these columns currently
> > suggests to me that they could be handled by a single timestamp column.
> > The cost/benefit of converting is another thing altogether.
>
> Rob,
>
> Since either way works I'll save the time cost of converting because my
> tables will never be really large.
>
Virtual columns exist: have both!
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Convert date and time colums to datetime
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 18:09 ` Re: Convert date and time colums to datetime Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:45 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 12:53 ` Re: Convert date and time colums to datetime Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:58 ` Re: Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
@ 2025-10-20 13:26 ` Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Rob Sargent @ 2025-10-20 13:26 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
> On Oct 20, 2025, at 6:58 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Mon, 20 Oct 2025, Rob Sargent wrote:
>
>>> Okay. Now I'm curious: why do you write this?
>
>> The way I read your description of how you use these columns currently
>> suggests to me that they could be handled by a single timestamp column.
>> The cost/benefit of converting is another thing altogether.
>
> Rob,
>
> Since either way works I'll save the time cost of converting because my
> tables will never be really large.
>
> Thanks,
>
> Rich
>
For sure! Your call
>
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2025-10-23 23:23 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-19 14:43 Convert date and time colums to datetime Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 14:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 14:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 16:35 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 16:46 ` Isaac Morland <isaac.morland@gmail.com>
2025-10-19 17:56 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 16:59 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-10-19 18:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-19 19:08 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-19 19:40 ` Rob Sargent <robjsargent@gmail.com>
2025-10-19 20:38 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 02:32 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 11:05 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-20 12:43 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:51 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-23 19:52 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2025-10-23 23:23 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:45 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 12:53 ` Rob Sargent <robjsargent@gmail.com>
2025-10-20 12:58 ` Rich Shepard <rshepard@appl-ecosys.com>
2025-10-20 13:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-10-20 13:26 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox