Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2022-06-30

Channels

#honeysql
<
2022-06-30
>
Otza04:06:30

Hi ! So, I got this project running on honeysql 1.x and the migration work isn’t scheduled for anytime soon. The code is hooked to a mysql database and I want to use https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html function to get change the timezone of a value using a timezone from a different column. > 

SELECT CONVERT_TZ (foo, 'Europe/Oslo','UTC');
How can I achieve that using the raw helper ?

seancorfield17:06:07

Are you looking for something like this: 

dev=> (sql/format {:select [(sql/call :convert_tz :foo "Europe/Oslo" "UTC")] :from [:table]})
["SELECT convert_tz(foo, ?, ?) FROM table" "Europe/Oslo" "UTC"]
dev=>
You could specify column names instead of either string. No need to use raw here.

seancorfield17:06:08

And just for comparison, in v2 you don't need call -- you can use the "regular" vector form for calls, but because of the possibility of column aliases in :select, you need extra nesting: 

dev=> (sql/format {:select [[[:convert_tz :foo "Europe/Oslo" "UTC"]]] :from [:table]})
["SELECT CONVERT_TZ(foo, ?, ?) FROM table" "Europe/Oslo" "UTC"]
dev=>
because this has to be possible: 
dev=> (sql/format {:select [:col1 [:col2 :alias2] [[:convert_tz :foo "Europe/Oslo" "UTC"] :alias3]] :from [:table]})
["SELECT col1, col2 AS alias2, CONVERT_TZ(foo, ?, ?) AS alias3 FROM table" "Europe/Oslo" "UTC"]
dev=>

Otza06:07:45

that worked ! thank you 😄