Expr CalendarDiffTime -> Expr Double ?

[Philoustic]

Hi,

I’m trying to convert an Expr CalendarDiffTime (which is an output of diffTime) to an Expr Double.

I have a function calendarDiffTimeToDoubleDays that works for converting from CalendarTime to Double.
However, since Expr isn’t a Functor, I’m not sure how to apply this conversion directly to the Expr type.

calendarDiffTimeToDoubleDays :: CalendarDiffTime -> Double
calendarDiffTimeToDoubleDays (CalendarDiffTime cmonths ctime) =
    monthsToDays + picosToDay
  where
    -- Convert months to days (approximation)
    monthsToDays = fromIntegral cmonths * 30.436875
    
    -- Convert picosecondes to days
    picosToDay = realToFrac $ ctime / nominalDay

Is there a way to achieve this transformation, or would I need to approach it differently?

Thanks for your help!

[tomjaguarpaw]

You can't run that Haskell function within the database, so you would have to run the query, get a CalendarDiffTime, and then apply calendarDiffTimeToDoubleDays. Would that work?

[Philoustic]

The issue is that I would liketo use 'diffTime' to obtain Double values for statistical analysis, but I'm receiving CalendarDiffTime
values instead (diffTime :: UTCTime -> UTCTime -> CalendarDiffTime) and am unsure how to convert them. As a result, I'm forced to use diffDays :: Day -> Day -> Integer after converting Time to Day with the 'today' function. However, working with days (as integers) is less precise than using Double values.

[tomjaguarpaw]

Do you want to use the Double values during the Postgres query? Or after you've run the query and obtained the results back in your Haskell process?

[Philoustic]

Let me show you a little more context:

newtype Los = Los {unLos :: Double}
    deriving newtype (Show, Eq, Ord, Num)
    deriving newtype(DBEq, DBOrd, DBType, DBNum)

Los (for Length Of Stay) is the type I use for statistics.
Los is build in several steps as follow:

-- | Exclude Maybe fields from CodingDB (using catNull)

data CodedDB f = CodedDB
  { ...
   , codedLos :: Column f Los
  , codedBy :: Column f Text
   ...
  }

Data mapping a database schema:

-- | Map some available database fields.
data CodingDB f = CodingDB
  ...
    , codingAdmitAt :: Column f UTCTime
    , codingDischargeAt :: Column f UTCTime
   ,  codingBy :: Column f (Maybe Text)
  ...
}
And somewhere in a function converting CodingDB to CodedDB:

eachCoded :: Query (CodedDB Expr)
eachCoded = do
...
pure $ CodedDB {
...
codedLos = unsafeCastExpr $ diffDays (toDay $ codingDischargeAt c) (toDay $ codingAdmitAt c)
...
}

This is the where I would like to use diffTime (or an alternative) instead of diffDay.
I hope this makes it a bit clearer...

[tomjaguarpaw]

Right, you can't use calendarDiffTimeToDoubleDays for that, because that's a Haskell function that runs in Haskell. You need a Postgres function that you can call from Rel8. I don't know whether there is such a function in Postgres, and if there is whether Rel8 exposes it.

[Philoustic]

This is what I feared.
I didn't find any rel8 function to convert CalendarDiffTime.
The functions are working in the opposite direction: e.g. days ;; Expr Double -> Expr CalendarDiffTime.

Postgresql can calculate the sum, avg, std on intervals (e.g. avg (t1 - t2 :: interval) -> interval).
This should be a great addition to rel8.

By now, I think the solution should be to have : 'Los :: Los CalendarDiffTime', and use calendarDiffTimeToDoubleDays during the statistical analysis.This will require some code refactoring (I hope not too much).

Any way, thank you for your insights.