I have some function calls that a repeated in my select statement and group-by statement. Is there anyway to reuse this Kind of like a let block? I'm using honey-sql however knowing how to do this in sql first is a good start

For example

(-> (select (->> (sql/call :/ :unix-timestamp-ms 1000)
                 (sql/call :to_timestamp)
                 (sql/call :date_trunc "year"))
            (sql/call :sum :grid-to-site-pre-calc-wh))
    (from :time-series)
    (where [:= :device-id "caleb-test-device-25"])
    (group :device-id
           :scenario-id
           (->> (sql/call :/ :unix-timestamp-ms 1000)
                (sql/call :to_timestamp)
                (sql/call :date_trunc "year")))
    sql/format)

I guess I could actually just use a let block:

(let [trunc-year (->> (sql/call :/ :unix-timestamp-ms 1000)
                      (sql/call :to_timestamp)
                      (sql/call :date_trunc "year"))]
  (-> (select trunc-year
              (sql/call :sum :grid-to-site-pre-calc-wh))
      (from :time-series)
      (where [:= :device-id "caleb-test-device-25"])
      (group :device-id
             :scenario-id
             trunc-year)
      sql/format))

@caleb.macdonaldblack You could probably do this with CTEs in SQL -- which will depend on which DB you're using, which version, and which driver.

postgres

Otherwise, this is exactly the benefit of HoneySQL: reusable, composable query fragments.

Yeah I'm loving honeySql for the fact that I can do that

I've no idea about PostgreSQL, nor CTEs, unfortunately. We use MySQL (Percona) and CTEs only came in 8.x (and we're still on 5.7).

http://postgresguide.com/cool/ctes.html

Nah I'm pretty sure they have it

Off the top of your head, do you know how I'd do this: extract(EPOCH FROM timestamp) in honeysql or honeysql-postgres?

I know I can do (sql/call :extract ???)

Not sure, sorry.

Okay all good. I might be able to extend it

@caleb.macdonaldblack does alias work for you, ie "SELECT ... AS trunc_year, ... GROUP BY ..., trunc_year"?

Yea that worked for me

It duplicates that part in the sql query but I think that's fine

actually sorry. Perhaps I misunderstood your question. it's not an alias

SELECT ..., trunc_year, ... GROUP BY ..., trunc_year

More like that. But trunc_year is not placed in the exactly like trunc_year

in the sql. It expands out

I meant "AS truct_year"

Yeah I can alias as well.

Generated SQL: (1000 * date_part('epoch', date_trunc('year', to_timestamp((unix_timestamp_ms / 1000))))) AS unix_timestamp_ms

(let [trunc-unix-timestamp-ms (->> (sql/call :/ :unix-timestamp-ms (sql/raw 1000))
                                     (sql/call :to_timestamp)
                                     (sql/call :date_trunc (sql/raw (str "'" (sanitize-aggregate aggregate) "'")))
                                     (sql/call :date_part (sql/raw "'epoch'"))
                                     (sql/call :* (sql/raw 1000)))]

(-> (select [trunc-unix-timestamp-ms :unix-timestamp-ms])

hmm, that honeysql code doesn't look pretty to my eyes 😞

(group trunc-unix-timestamp-ms)

I'm definitely open to feedback if you have any 😛

I don't like how I've done used sql/raw. But strings didn't work for me

I'm not a honeysql user at all

walkable?

yeah

Yeah I'm briefly looking through the docs now.

if you use walkable, for your case you'll define a "pseudo-column" :some-table/trunc-year

then you can use that column everywhere, including group-by

That's interesting.

{:pseudo-column {:trunc_unix_timestamp_ms 
               [:* 1000
                [:date_part "epoch"
                 [:date_trunc 'aggregate
                  [:to_timestamp
                   [:/ :time-series/unix-timestamp-ms 1000]]]]]}}

or maybe not :aggregators'

what is the symbol aggregate there?

#{"day" "month" "year"}

any one of those

as a string

a "variable", right?

perhaps there is a different way to pass in variables?

yeah

oh I would need to quote it

yeah, "variable" is its own term in walkable

Hi, I am using clojure.java.jdbc with postgresql. At the time of table creation, if I provide [:age :int :default 18] then jdbc throws error (`java.lang.IllegalArgumentException: column spec is not a sequence of keywords / strings`), but it accepts "18", which is logically incorrect. How to handle this?

The schema shows the int annotation though - 18:::INT under column_default

DDL is notoriously database-specific so clojure.java.jdbc deliberately doesn't try to offer much in that area. I recommend you look at honeysql and, since you are using PostgreSQL, honeysql-postgres to build richer DDL.

That said, accepting numbers in the column spec is a reasonable enhancement so I'll create a JIRA issue for that...

... https://dev.clojure.org/jira/browse/JDBC-177 @amarjeet