public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rob Sargent <[email protected]>
To: Peter J. Holzer <[email protected]>
Cc: [email protected]
Subject: Re: Convert date and time colums to datetime
Date: Thu, 23 Oct 2025 17:23:50 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm>
References: <rzyu6wzxgjp6qnrryqt2ed4lliyyvclqcpf44gldxxnldn6hhy@xsrs2xks2cfm>
> On Oct 23, 2025, at 1:52 PM, Peter J. Holzer <[email protected]> wrote:
>
> 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
>
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.
> |_|_) | |
> | | | [email protected] | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
> <signature.asc>
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], [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