public inbox for [email protected]
help / color / mirror / Atom feedRe: Convert date and time colums to datetime
4+ messages / 3 participants
[nested] [flat]
* Re: Convert date and time colums to datetime
@ 2025-10-20 11:05 Peter J. Holzer <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Peter J. Holzer @ 2025-10-20 11:05 UTC (permalink / raw)
To: [email protected]
On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
> > On Oct 19, 2025, at 2:38 PM, Rich Shepard <[email protected]> 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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert date and time colums to datetime
@ 2025-10-20 12:43 Rob Sargent <[email protected]>
parent: Peter J. Holzer <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Rob Sargent @ 2025-10-20 12:43 UTC (permalink / raw)
To: Peter J. Holzer <[email protected]>; +Cc: [email protected]
> On Oct 20, 2025, at 5:05 AM, Peter J. Holzer <[email protected]> wrote:
>
> On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
>>>> On Oct 19, 2025, at 2:38 PM, Rich Shepard <[email protected]> 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.
> |_|_) | |
> | | | [email protected] | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
> <signature.asc>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert date and time colums to datetime
@ 2025-10-20 12:51 Rich Shepard <[email protected]>
parent: Rob Sargent <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Rich Shepard @ 2025-10-20 12:51 UTC (permalink / raw)
To: [email protected]
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] 4+ messages in thread
* Re: Convert date and time colums to datetime
@ 2025-10-23 19:52 Peter J. Holzer <[email protected]>
parent: Rob Sargent <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Peter J. Holzer @ 2025-10-23 19:52 UTC (permalink / raw)
To: [email protected]
On 2025-10-20 06:43:17 -0600, Rob Sargent wrote:
>
>
> > On Oct 20, 2025, at 5:05 AM, Peter J. Holzer <[email protected]> wrote:
> >
> > On 2025-10-19 20:32:07 -0600, Rob Sargent wrote:
> >>>> On Oct 19, 2025, at 2:38 PM, Rich Shepard <[email protected]> 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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-10-23 19:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-20 11:05 Re: Convert date and time colums to datetime Peter J. Holzer <[email protected]>
2025-10-20 12:43 ` Rob Sargent <[email protected]>
2025-10-20 12:51 ` Rich Shepard <[email protected]>
2025-10-23 19:52 ` Peter J. Holzer <[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