PostgreSQL time intervals in Clarion

Question

How to rewrite this query from PostgreSQL in Clarion?

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

Answer 1

When you ask "How to rewrite select extract(epoch from (timestamp '02-01-2021 06:00' - timestamp '01-01-2021 22:00'))/3600 as interval_as_hours_numeric" in Clarion, I'm assuming you mean do the date/time math using Clarion syntax.

If you want to use this query via the Clarion file drivers and send it to PostgreSQL, you can use this code:

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) 

The result will be in the first field in the mySQLtable record structure.

However, I suspect that isn't what you wanted, so I'll continue as if you wanted to do this with Clarion syntax. I will also assume you're relatively new to Clarion, so I may explain things you already know.

Clarion does not have a timestamp data type. It supports DATE and TIME. It also supports SQL DATETIME types indirectly by combining the DATE and TIME data types using a GROUP() and an OVER(). Any combination of the DATE and TIME must be managed by the dev using tactics of this nature. As a result, the timestamp's date and time portions must be parsed separately.

In Clarion, you can parse date and time values by hand or via the Clarion DEFORMAT() verb. I do this by hand in a class method so that I don't have to worry about DEFORMAT behavior changes in the future. To keep it simple, we'll use DEFORMAT().

There are other (better) ways to do this, but this provides what you need in the form of an explanation rather than a perhaps more obtuse class method that might not explain WHY the code does what it does.

Some sample code...

 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