Forum > Databases

about sqldb postgresql LOCALTIMESTAMP

(1/3) > >>

gucao:
CN:
背景:我需要按时间提取即将超时的客人信息。
复现步骤:在没有客人超时之前先查询一遍数据,得到空的数据集,这是正确的。但是当有客人超时以后查询,仍然没有得到任何数据。
暂时的解决方法:使用params代替sql代码中的LOCALTIMESTAMP(0)。
有经验的朋友们能否确认一下这是不是sqldb的问题。

from Translation tools:
Background: I need to fetch guest information that is about to time out by time.

Steps to reproduce: It is correct to query the data and get an empty data set before there is no guest timeout. But when a guest queries after timeout, still no data is obtained.

Temporary workaround: Use params instead of LOCALTIMESTAMP(0) in the sql code.

Can experienced friends confirm whether this is a problem with sqldb.

i'm use lazarus 2.2.0  postgresql 14.2


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.TestQueryClick(Sender: TObject);begin  with SQLQuery2 do  begin    Active:=False;    SQL.Clear;    if UseParam.State=cbChecked then begin      //this is OK      SQL.Add('Select * from atemp where ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM :NowTime))) < 5');      Params.ParamByName('NowTime').AsDateTime:=Now;    end else begin      //this not OK      SQL.Add('Select * from atemp where ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM LOCALTIMESTAMP(0)))) < 5');    end;    Open;  end;end;   

Red_prig:
Information is quite small, but still try in sql something like this:
--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Select :NowTime it will help to localize the problem. Or maybe your database gives a syntax error and that's why it's empty.

gucao:

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT * FROM atemp WHERE ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM LOCALTIMESTAMP(0)))) < 5 This SQL code works fine in PGADmin4, as shown in the second picture.

cn:
当没有任何超时客人时,用sqldb进行了一次查询,从此以后 不会再返回数据,就算有客人超时了,也不会再返回数据。但是,如果第一次查询就存在超时客人,以后每次查询都是正常的。

from trans:
When there is no time-out guest, a query is made with sqldb, and no more data will be returned from now on, even if a guest times out, no more data will be returned. However, if there is a timeout guest in the first query, every subsequent query will be normal.

Zvoni:
localtimestamp returns local time WITHOUT timezone-offset
Basically: It returns UTC

You are in China. You are 8 hours before UTC.
Try current_timestamp or NOW()

gucao:

--- Quote from: Zvoni on May 17, 2022, 09:09:15 am ---localtimestamp returns local time WITHOUT timezone-offset
Basically: It returns UTC

You are in China. You are 8 hours before UTC.
Try current_timestamp or NOW()

--- End quote ---

This is not the reason, because at the same time, the same sql code, the data can be found in pgadmin.

Navigation

[0] Message Index

[#] Next page

Go to full version