PostgreSQL时间间隔(以Clarion为单位

Question

如何在Clarion中从PostgreSQL重写这个查询？

select extract(epoch from (timestamp '02-01-2021 06:00' - timestamp '01-01-2021 22:00'))/3600 as interval_as_hours_numeric

Answer 1

当你在Clarion语言中问“如何重写select extract(epoch from (timestamp '02-01-2021 06:00‘- timestamp '01-01-2021 22:00’)/3600 as interval_as_hours_numeric”)时，我想你的意思是使用Clarion语法计算日期/时间。

如果您希望通过Clarion文件驱动程序使用此查询并将其发送到PostgreSQL，则可以使用以下代码：

mySQLtable{prop:SQL} = 'select extract(epoch from (timestamp <39>02-01-2021 06:00<39> - timestamp <39>01-01-2021 22:00<39>))/3600 as interval_as_hours_numeric'
NEXT(mySQLtable) 

结果将出现在mySQLtable记录结构的第一个字段中。

但是，我怀疑这不是您想要的，所以我将继续使用Clarion语法。我还假设您对Clarion比较陌生，所以我可能会解释一些您已经知道的事情。

Clarion没有时间戳数据类型。它支持日期和时间。它还通过使用GROUP()和OVER()组合DATE和TIME数据类型来间接支持SQL DATETIME类型。日期和时间的任何组合都必须由开发人员使用这种策略进行管理。因此，必须分别解析时间戳的日期和时间部分。

在Clarion中，您可以手动或通过Clarion DEFORMAT()动词解析日期和时间值。我在一个类方法中手工完成这项工作，这样我就不必担心将来会发生变形行为的变化。为了简单起见，我们将使用DEFORMAT()。

还有其他(更好的)方法可以做到这一点，但这以解释的形式提供了您需要的东西，而不是可能无法解释代码为什么会这样做的可能更迟钝的类方法。

一些示例代码..。

 PROGRAM

 MAP 
 END 

Time:Tick            EQUATE(1)
Time:Second          EQUATE(100 * Time:Tick)    ! there are 100 ticks in a second
Time:Minute          EQUATE(60 * Time:Second)
Time:Hour            EQUATE(60 * Time:Minute)
Time:Day             EQUATE(24 * Time:Hour)
Time:Midnight        EQUATE(1)                  ! timevariable=0 means "no time". timevariable=1 means midnight.
Time:FullDay         EQUATE(Time:Midnight + 23 * Time:Hour + 59 * Time:Minute + 59 * Time:Second + 99 * Time:Tick)
Time:AlsoFullDay     EQUATE(8640000)            ! the easy way to get 24 hours worth of Clarion time

strMyDate            STRING(10)
dMyDate              DATE  ! I tend to use LONGs for dates. Old habits, I guess. It doesn't really matter. 

strMyLaterDate       STRING(10) 
dMyLaterDate         DATE 

strMyTime            STRING(5)
tMyTime              TIME
intMyTime            LONG 

strMyLaterTime       STRING(5)
tMyLaterTime         TIME
intMyLaterTime       LONG 

intMyDate            LONG 
intDifferenceInHours LONG 

decDifferenceInHours DECIMAL(3,2)

intDifferenceInDays  LONG 

 CODE

 strMyDate = '01-01-2021'
 dMyDate = DEFORMAT(strMyDate,@D06-)  ! look in the help for "Date Pictures" to see why I used @D02. 

 ! at this point, dMyDate contains 01-01-2021 (Jan 1, 2021) represented as a "Clarion Standard Date", which is the number of days elapsed since December 28, 1800. 

 strMyLaterDate = '02-01-2021'
 dMyLaterDate = DEFORMAT(strMyLaterDate,@D06-) 

! at this point, dMyLaterDate contains 02-01-2021 (Jan 2, 2021) represented as a Clarion Standard Date.


 strMyTime = '22:00'
 tMyTime = DEFORMAT(strMyTime,@T01)  ! look in the help for "Date Pictures" to see why I used @D02. 

 ! at this point, tMyTime contains 22:00 represented as a "Clarion Standard Time", which is the number of ticks since midnight. 
 ! A Clarion tick is 1/100th of a second. See the Time: equates above. 

 strMyLaterTime = '06:00'
 tMyLaterTime = DEFORMAT(strMyLaterTime,@T01) 

! at this point, tMyLaterTime contains 06:00 represented as a Clarion Standard Time. 

! Because there isn't a native datetime datatype in Clarion, we must do the math ourselves. This is a bit crude and not at all
! how I'd write this in a class, but I wanted it to function more as an explainer than as production code. 


! How do the dates match up?

 CASE dMyLaterDate - dMyDate
    OF -9999 to -1 ! dMyLaterDate is an earlier date than dMyDate 
       ! this code will be similar to the code below under "ELSE !dMyLaterDate is a later day than dMyDate"

    OF 0 ! same date, different times.

       IF tMyTime < tMyLaterTime 
          intDifferenceInHours = (tMyLaterTime - tMyTime) / Time:Hour
          decDifferenceInHours = (tMyLaterTime - tMyTime) / Time:Hour
       ELSIF tMyTime > tMyLaterTime 
           intDifferenceInHours = (tMyTime - tMyLaterTime) / Time:Hour
           decDifferenceInHours = (tMyTime - tMyLaterTime) / Time:Hour
       ELSE
          intDifferenceInHours = 0
          decDifferenceInHours = 0
       END 

   ELSE !dMyLaterDate is a later day than dMyDate (such as your example)
      intDifferenceInDays = dMyLaterDate - dMyDate
      intDifferenceInHours = Time:FullDay - tMyTime +        | ! partial day associated wth tMyDate, ie: from 22:00 to midnight
                             tMyLaterTime - Time:Midnight +  | ! partial day associated with tMyLaterDate, ie: from midnight to 06:00
                             (intDifferenceInDays - 1) * 24 * Time:Hour + 1  ! Difference in full days. The 1st day doesnt count (thus the -1), 
                                                                             ! as the times take care of that part of the difference.

     ! a little easier to read                            
     ! intDifferenceInHours = Time:FullDay - tMyTime + tMyLaterTime - Time:Midnight + (intDifferenceInDays - 1) * 24 * Time:Hour 

 END 
 
 MESSAGE('diff in hours=' & intDifferenceInHours / Time:Hour)
 
 RETURN