Forum > Databases
about sqldb postgresql LOCALTIMESTAMP
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