Recent

Author Topic: about sqldb postgresql LOCALTIMESTAMP  (Read 893 times)

gucao

  • New Member
  • *
  • Posts: 31
about sqldb postgresql LOCALTIMESTAMP
« on: May 16, 2022, 06:59:24 pm »
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  [Select][+][-]
  1. procedure TForm1.TestQueryClick(Sender: TObject);
  2. begin
  3.   with SQLQuery2 do
  4.   begin
  5.     Active:=False;
  6.     SQL.Clear;
  7.     if UseParam.State=cbChecked then begin
  8.       //this is OK
  9.       SQL.Add('Select * from atemp where ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM :NowTime))) < 5');
  10.       Params.ParamByName('NowTime').AsDateTime:=Now;
  11.     end else begin
  12.       //this not OK
  13.       SQL.Add('Select * from atemp where ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM LOCALTIMESTAMP(0)))) < 5');
  14.     end;
  15.     Open;
  16.   end;
  17. end;  
  18.  
« Last Edit: May 16, 2022, 07:25:50 pm by gucao »

Red_prig

  • Jr. Member
  • **
  • Posts: 60
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #1 on: May 16, 2022, 09:00:58 pm »
Information is quite small, but still try in sql something like this:
Code: Pascal  [Select][+][-]
  1. 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

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #2 on: May 17, 2022, 04:52:22 am »
Code: SQL  [Select][+][-]
  1. SELECT * FROM atemp WHERE ((EXTRACT(EPOCH FROM atime) - EXTRACT(EPOCH FROM LOCALTIMESTAMP(0)))) < 5
  2.  
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

  • Hero Member
  • *****
  • Posts: 1276
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #3 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()
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #4 on: May 17, 2022, 02:25:49 pm »
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()

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

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #5 on: May 17, 2022, 02:29:09 pm »
My English is not good, maybe you don't understand what I'm saying  :'(  . Can someone tell me where sqldb receives data from postgresql? Maybe I should go there and debug it.

Zvoni

  • Hero Member
  • *****
  • Posts: 1276
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #6 on: May 17, 2022, 02:32:26 pm »
OK, but it still has nothing to do with sqldb.

Do a Writeln of "Now"
and do a Writeln of "SELECT LOCALTIMESTAMP(0)"
and compare
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #7 on: May 17, 2022, 02:49:24 pm »
OK, but it still has nothing to do with sqldb.

Do a Writeln of "Now"
and do a Writeln of "SELECT LOCALTIMESTAMP(0)"
and compare
« Last Edit: May 17, 2022, 02:53:47 pm by gucao »

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #8 on: May 17, 2022, 03:28:18 pm »
try it Before the minimum timeout period:
all data:
aid=1;OutTime=2022-05-17 21:17:00
aid=2;OutTime=2022-05-17 21:17:10
aid=3;OutTime=2022-05-17 21:17:20

2022-05-17 21:16:48 Call TestQueryClick RecordCount=0    //21:16:48<21:17:00  it's right 
2022-05-17 21:17:07 Call TestQueryClick RecordCount=0    //21:17:07>21:17:00  it's Should be 1
2022-05-17 21:17:24 Call TestQueryClick RecordCount=0    //21:17:24>21:17:20  it's Should be 3

use param try it
all data:
aid=1;OutTime=2022-05-17 21:35:08
aid=2;OutTime=2022-05-17 21:35:18
aid=3;OutTime=2022-05-17 21:35:28

2022-05-17 21:34:56 Call TestQueryClick RecordCount=0  //21:34:56<21:35:08  it's  right
2022-05-17 21:35:10 Call TestQueryClick RecordCount=1  //21:35:08<21:35:10<21:35:18 it's right
2022-05-17 21:35:33 Call TestQueryClick RecordCount=3  //21:35:33>21:35:28  it's right
« Last Edit: May 17, 2022, 03:41:48 pm by gucao »

Zvoni

  • Hero Member
  • *****
  • Posts: 1276
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #9 on: May 17, 2022, 04:58:16 pm »
I was just looking at his Query again
Is it just me, or should the Where-Clause always return True?
i'm guessing atime would always be smaller than Now or LocalTimeStamp, so atime - Now/LTS <0 negative!
So "<5" should always be true.......

Something else: Are you really checking for a difference of smaller than 5 seconds?

lastly
Quote
2022-05-17 21:34:56 Call TestQueryClick RecordCount=0  //21:34:56<21:35:08  it's  right
No it's not. 21:34:56 to 21:35:08 is 12 seconds difference, not the 5 you are looking for.
Depending on if your WHERE-Clause actually returns a positive value

have you tried with BETWEEN-Operator?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MarkMLl

  • Hero Member
  • *****
  • Posts: 4778
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #10 on: May 17, 2022, 05:41:33 pm »
I admit to being uncomfortable with what I see there. PostgreSQL has a comprehensive- and in my experience with it over 20 years- reliable set of timestamp and interval manipulation and comparison operations, and it should definitely not be necessary to drill down to the epoch like that.

I also suggest that the relationship between the unix epoch and timestamps that might have been derived from different OSes is complex. See the first paragraph of https://en.wikipedia.org/wiki/Unix_epoch, and consider

Code: [Select]
TDateTime: 8 bytes, extended: 10 bytes
Local time: 2022-05-08 10:23:20.9
UTC: 2022-05-08 09:23:20.9
Unix secs: 1652005400
POSIX secs: 1652001800.93093 (2022-05-08 09:23:20.9)

which was noted at the startup of a long-running program on the (Debian Linux) system in front of me. Now in that particular case the difference between monotonically-incrementing unix time and the POSIX time is mostly accounted for by the DST correction, but I /have/ seen larger differences than the 0.93 seconds visible there.

Finally, I admit to being extremely uncomfortable with a query which compares timestamps stored on the server with a value injected from a client system... or vice-versa. My preference is for the server to never attempt to apply DST corrections, and for tables to contain both server- and client-generated timestamps with the appropriate ones selected according to the requirements of an operation.

MarkMLl
« Last Edit: May 17, 2022, 06:39:21 pm by MarkMLl »
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #11 on: May 18, 2022, 02:48:58 am »
Something else: Are you really checking for a difference of smaller than 5 seconds?
i'm sorry,I'm  change the 5 to 0 just to make the comparison easier,Sorry to confuse you.
« Last Edit: May 18, 2022, 03:01:50 am by gucao »

gucao

  • New Member
  • *
  • Posts: 31
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #12 on: May 18, 2022, 02:56:58 am »

Finally, I admit to being extremely uncomfortable with a query which compares timestamps stored on the server with a value injected from a client system... or vice-versa. My preference is for the server to never attempt to apply DST corrections, and for tables to contain both server- and client-generated timestamps with the appropriate ones selected according to the requirements of an operation.


Great advice,thank you.

MarkMLl

  • Hero Member
  • *****
  • Posts: 4778
Re: about sqldb postgresql LOCALTIMESTAMP
« Reply #13 on: May 18, 2022, 10:23:35 am »

Finally, I admit to being extremely uncomfortable with a query which compares timestamps stored on the server with a value injected from a client system... or vice-versa. My preference is for the server to never attempt to apply DST corrections, and for tables to contain both server- and client-generated timestamps with the appropriate ones selected according to the requirements of an operation.


Great advice,thank you.

To expand somewhat on that. In principle, a server- particularly a unix-based server- would be expected to have its hardware clock set to either UTC or to a fixed (and known) offset from UTC, with any file operations timestamped UTC and a database able to save UTC timestamps and use UTC for interval calculation. Things can still get pretty hairy, however, when it attempts to present the date and time associated with a file with a "correction" for local timezone and DST ("summer time") offset.

I've learnt the hard way to be paranoid about this.

I once had a situation where I had a number of data files bearing locally-generated timestamps (UK) representing data that had been collected from various sites in the UK and mainland Europe on a date where DST applied to some and not to others. I ended up with something like five or six distinct sets of times, depending on whether a file had been timestamped (after its arrival in the UK) with or without DST, whether the site originating the data was in a jurisdiction currently applying DST, and the precise timezone rules that the database server was applying. And I had to work out what was going on with unsympathetic senior management breathing down my neck.

After that I was extremely careful to ensure that any system that either generated or displayed timestamps had its hardware clock set to UTC and kept it that way, i.e. with no DST correction.

I was also careful to ensure that any timestamp information that arrived was saved in its original form on a record-by-record basis, even if it was known to originate from a 146818 chip (embedded system geeks will recognise that number and shudder), as well as logging the timestamp of any intermediate file (which /should/ be UTC) and the timestamp of the database's own insertion operation (which again, /should/ be UTC... if a database can't get this unambiguously there's something badly wrong).

Now I set that up 20 years or so ago, and it's still ticking along. But that's 20 years further away from the epoch, which means that any timestamp that uses a floating-point representation is obliged to put more bits into the integer part hence has less precision for the fractional part. And that's another good reason for avoiding TDateTime and Now() in time calculations.

You also have to take into account that different computers have internal clocks drifting at different rates, and will compensate differently when they receive a time update from the Internet... or from a local (NTP etc.) server, which might have its own issues. And don't get me going on GPS...

Ite, missa est
Pax vobiscum.

MarkMLl
« Last Edit: May 18, 2022, 12:38:32 pm by MarkMLl »
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

 

TinyPortal © 2005-2018