Re: Convert date and time colums to datetime

> 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
seriouslyadvocating 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>