I am searching for ways to populate a Go template with sql values. So far I have 3 solutions that has pros and cons. However the date displays in a funny way.
From PGAdmin the date displays correct 2020-08-26
Using sql standard library the date displays 2020-08-26 00:00:00 +0000 +0000
Using sqlx the date displays 2020-08-26T00:00:00Z
Using json the date displays correct 2020-08-26
Or is there no date format in Golang Struct?
1. Sql standard library
Standard sql library is idiomatic, but you have to repeat the structs twice.
3. JSON experiment
This is DRY at the highest level, but I am considering it as a dead end. Because the function json_agg() does not work with joins etc. But I still consider it is an option for some cases.
I see from your play.golang.org examples how you’re getting the data out of SQL, but how are you preparing it for viewing? Are you letting the default time.Time.String function format it? I think you’re looking for time.Time.Format which lets you specify the exact format you want (e.g. hr := get().([]Hr)[0]; hr.Date.Format("2006-01-02");).
type Hr struct {
Date time.Time
Sign string
Code string
Qty float64
Sum float64
}
How should I prepare? I find it logic to prepare it by setting the type in the Struct. Or am I thinking wrong?
What is the purpose of “Struct”? Is it not to set the type of a column? Is there no “date” column in Go?
And how and when do I implement time.Time.Format in a query? In the query or when looping “for rows.Next()”? And how do I implement this with sqlx? Or should i put it in the struct?
How are you using these Hr structures? Are they only for display purposes (e.g. are they just the models for that table on your site)? If so, then you could use the string type for your Date field. If you have any code that uses that Date field (e.g. filtering for certain dates), then you should probably use the time.Time type.
If you use the time.Time type, then you don’t have to parse the time out; (*sqlx.Rows).StructScan handles reading the value as a “time-type” (e.g. date, datetime, datetime2, whatever comes out of the RDBMS). Your issue then becomes that when you want to display it, if you don’t want the default long output you get from time.Time.String, you have to instead call time.Time.Format and give it a format string.
To be honest, I do not know what I am doing. All I have learned for many years does not apply in Go. Struct to me is a description of column types in a table with at least basic formatting capabilities. Short date or date+time. There is a library
that seems to do what I am looking for, but I have not got it to work yet. And I do not know if it works as a struct column type.
IMHO Go lacks of a standard date library. To use in a struct.
I expect that Go should not alter the values from the database. The database is already casted in a correct format. Why should Go alter the values?
I’m not sure if this helps you or not, but here I’m demonstrating how you can have a time.Time struct field but a template where I want a specific format: https://play.golang.org/p/LqCTJ41xLr_1
Is there a way to make this more “generic”? I e make a custom “ISO” type under the Hr struct? I know that generic is a forbidden word in Golang, but I am just curious. Pseudocode:
type ISO func () {
Date.Format("2006-01-02")
}
type Hr struct {
Date *ISO `db:"hr_date"` //Generic custom type
Sign string `db:"hr_sign"`
Code string `db:"hr_code"`
Qty float64 `db:"hr_qty"`
Sum float64 `db:"hr_sum"`
}
Why do you need the SQL query to not include a timestamp? I changed my example here to include hours, minutes and seconds in the GetModel function to represent how records could be loaded into a slice with sqlx or database/sql, but because I’m formatting the output, it doesn’t include that time when displaying. If you’re trying to do it with SQL then you can cast the datatype in SQL. If you’re using MS SQL Server, you can use cast(hr_date as date) or convert(date, hr_date), etc.
Why do I want “2006-01-02” without timezone?
As someone pointed out somewhere - Date of Birth looks nicer without milliseconds. So it is mostly a display question. As the output of Postgresql is “2006-01-02”, I want to keep this format without adding milliseconds to a DoB. And according to wikipedia, the only countries that use the MM/DD/YYYY system are the US, the Philippines, Palau, Canada, and Micronesia. So the ISO 8901 is a worldwide standard that should be supported.
My newbie conclusion:
Right or wrong. Go does not support ISO 8601 short date format in one step. This conclusion is made from this sources:
ISO 8601 is supported by several workarounds. But my experience is that every extra line takes power and adds extra time to the execution. Or makes it harder to read and maintain.
How about RFC3339?
RFC3339 should be the same as ISO 8601 (I think), but there is still no way to use it in a struct
type Hr struct {
Date time.RFC3339 `db:"hr_date"`
Sign string `db:"hr_sign"`
Code string `db:"hr_code"`
Qty float64 `db:"hr_qty"`
Sum float64 `db:"hr_sum"`
}
error: time.RFC3339 is not a type
Workaround 1 Double Structs
This is a brilliant way to overcome the lack of ISO support.
type HrDB struct {
Date time.Time `db:"hr_date"`
Hr
}
type Hr struct {
Date string
Sign string `db:"hr_sign"`
Code string `db:"hr_code"`
Qty float64 `db:"hr_qty"`
Sum float64 `db:"hr_sum"`
}
However this does not eliminate the parsing of all dates involved. You have to specifically address every date column involved.
time.Parse("2006-01-02", row.Date) time.Parse("2006-01-02", row.Edit) and so on
Workaround 2 use string
This is probably the simplest way to handle hundreds of structs. But still not simple.
type Hr struct {
Date string `db:"hr_date"`
Sign string `db:"hr_sign"`
Code string `db:"hr_code"`
Qty float64 `db:"hr_qty"`
Sum float64 `db:"hr_sum"`
}
list := []Hr{}
for rows.Next() {
row := Hr{}
err := rows.StructScan(&row)
if err != nil {
log(err.Error())
continue
}
row.Date = row.Date[:10]
list = append(list, row)
}
return (list)
Workaround 3 cast in Postgresql
Cast in Postgresql is simpler, but have limitations:
"SELECT hr_date::text, hr_sign, hr_code, hr_qty, hr_sum FROM hr"
Casting all columns will not work
"SELECT *::text FROM hr"
What am I looking for?
I am looking for a way that I can use Structs as “column types” with limited formatting capabilities. I e one step solution. I have seen many non standard libraries that claims that i should work this way, but I have got none of them working.
type Hr struct {
Date date.ISO8601 `db:"hr_date"`
Sign string `db:"hr_sign"`
Code string `db:"hr_code"`
Qty float64 `db:"hr_qty"`
Sum float64 `db:"hr_sum"`
}
I am thankful for all helpful answers I get and I find that the “string solution” from @petrus is the closest and simplest. Though not the way a newbie is thinking
