Feedback-ID: i76984098:Fastmail
Message-ID: <567f0d1f-c130-4abb-b2e4-dd6abdf89549@aklaver.com>
Date: Thu, 5 Dec 2024 18:15:43 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Parameter in SQL query being misinterpreted
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Daniel Johnson <djohnson@progman.us>, psycopg@lists.postgresql.org
References: <dfc11d8e-dc27-4c72-a2aa-87b2150dc331@progman.us>
 <cc13d120-44c1-4d13-b478-57015d54fc1e@aklaver.com>
 <c9023cca-a193-4430-912d-5cebe6ce7c4c@aklaver.com>
Content-Language: en-US
In-Reply-To: <c9023cca-a193-4430-912d-5cebe6ce7c4c@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/567f0d1f-c130-4abb-b2e4-dd6abdf89549%40aklaver.com>
Precedence: bulk

On 12/5/24 18:00, Adrian Klaver wrote:
> On 12/5/24 17:37, Adrian Klaver wrote:
>> On 12/5/24 17:27, Daniel Johnson wrote:

>> You might try something like:
>>
>> NOW() + INTERVAL || ' ' ||  %(NextDBLog)s || ' SECOND'
>>
>> If that does work then you will need to use the psycopg.sql module to 
>> build that part of the query
>>>
> 
> 
> What I ended getting to work:
> 
> from psycopg import sql
> 
> cur.execute(sql.SQL("select now(), now() + ({} || ' seconds')::interval 
> ").format(sql.Literal(4)))
> 
> cur.fetchone()
> (datetime.datetime(2024, 12, 5, 17, 57, 55, 670218, 
> tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')),
>   datetime.datetime(2024, 12, 5, 17, 57, 59, 670218, 
> tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')))
> 
> 

Then there is the alternative I keep forgetting about, the function 
make_interval():

https://www.postgresql.org/docs/current/functions-datetime.html

make_interval ( [ years int [, months int [, weeks int [, days int [, 
hours int [, mins int [, secs double precision ]]]]]]] ) → interval

Then you could pass parameters to the function.

-- 
Adrian Klaver
adrian.klaver@aklaver.com