Unsafe Sql for Generate Time Series

[parthshah31]

Looking to write the following SQL query in esqueleto:

SELECT distinct date_trunc('hour', dd)
FROM generate_series ('2007-02-01 02:02:01', '2008-04-01 00:01:01', '1 hour'::interval) dd
order by date_trunc('hour', dd);

Curious how to do the generate_series in a composable way. Most the unsafeSql examples only have a single SELECT statement. Here the complexity is on the FROM side. Ideas?

I have a date_trunc function I've written as part of a larger extensions library: https://github.com/polimorphic/esqueleto-extensions/blob/main/src/Database/Esqueleto/Datetime.hs.

[parsonsmatt]

I feel like you can do this using the Experimental syntax:

select $ distinct $ do
    dd <- from $ generateSeries begin end interval
    orderBy $ dateTrunc "hour" dd
    pure $ dateTrunc "hour" dd

generateSeries :: SqlExpr (Value UTCTime) -> SqlExpr (Value UTCTime) -> SqlExpr (Value Interval) -> SqlQuery (Value UTCTime)
generateSeries begin end interval = 
  pure $ unsafeSqlFunction "generate_series" begin end interval 

But I may be wrong here - please let me know how this works for you!

[parthshah31]

Awesome will try that out! Re: Interval type did this https://github.com/bitemyapp/esqueleto/issues/263 ever get resolved? Or should I just use an unsafeSqlValue there? I figure a (SqlString a) => .... -> SqlExpr (Value a) -> ... will not work since it needs the :: interval when you write the query in SQL

[parthshah31]

This worked! Added implementation to extensions library here for component use: https://github.com/polimorphic/esqueleto-extensions/blob/main/src/Database/Esqueleto/Datetime.hs