How to display date without time

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.

https://play.golang.org/p/lmrTCHbOwQW
http://94.237.92.101:5051/

2. Sqlx
Sqlx is an add on to standard library with “StructScan” which makes it more generic. I interpret this is still an idiomatic way.

https://play.golang.org/p/abx0OQJmgyc
http://94.237.92.101:5052/

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.

https://play.golang.org/p/kmu1l75u2N4
http://94.237.92.101:5053/

My questions

  1. How do I get rid of the time garbage (“T00:00:00Z”) in 1 and 2? Is there a way to use time.Date(YYYY-MM-DD) in a struct sort of?
  2. Is there more ways to achieve this? Simpler or better?
1 Like

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");).

1 Like
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?

I have tried this (sqlx):

list := []Hr{}
	for rows.Next() {
		row := Hr{}
		hr := get().([]Hr)[0]
		hr.Date.Format("2006-01-02")
		err := rows.StructScan(&row)
		if err != nil {
			log(err.Error())
		}
		list = append(list, row)
	}

but gets an error: hr.Date.Format undefined (type string has no field or method Format)

For your sqlx example where Hr.Date is a string,

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
	}
	if len(row.Date) >= 10 {
		row.Date = row.Date[:10]
	}
	list = append(list, row)
}
return (list)

https://play.golang.org/p/NIVorhFm9KS

1 Like

Yes. This works. Thank you!

But how do I format if the struct was time.Time? This does not work.

type Hr struct {
	Date time.Time `db:"hr_date"`
	Sign string    `db:"hr_sign"`
	Code string    `db:"hr_code"`
	Qty  float64   `db:"hr_qty"`
	Sum  float64   `db:"hr_sum"`
}

func get() interface{} {
	rows, err := db.Queryx("SELECT *::text FROM hr")
	if err != nil {
		log("getsql error" + err.Error())
	}
	defer rows.Close()

	list := []Hr{}
	for rows.Next() {
		row := Hr{}
		err := rows.StructScan(&row)
		if err != nil {
			log(err.Error())
		}
		time.Parse("2006-01-02", row.Date)
		list = append(list, row)
	}

	return (list)
}

Error: cannot use row.Date (type time.Time) as type string in argument to time.Parse

1 Like

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.

2 Likes

Try using an auxiliary data structure HrDB.

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"`
}

.

list := []Hr{}
for rows.Next() {
	row := HrDB{}
	err := rows.StructScan(&row)
	if err != nil {
		log(err.Error())
	}
	row.Hr.Date = row.Date.Format("2006-01-02")
	list = append(list, row.Hr)
}
return (list)

https://play.golang.org/p/0Qt2AqT5__x

The code compiles but I am unable to test it.

1 Like

Thanks! It works as expected. I could never figured out this myself. String + Time :slight_smile:

1 Like

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?

The Google Cloud Go date package: Package civil.

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

But how do I use this? There are no structs example in the documentation.

type Hr struct {
	Date civil.Date `db:"hr_date"`
	Sign string     `db:"hr_sign"`
	Code string     `db:"hr_code"`
	Qty  float64    `db:"hr_qty"`
	Sum  float64    `db:"hr_sum"`
}

Error: *Scan error on column index 0, name “hr_date”: unsupported Scan, storing driver.Value type time.Time into type civil.Date

I cannot translate this how to format a sql query into ISO standard without timestamp.

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"`
}
type HrDB struct {
	Date time.Time `db:"hr_date"`
	Hr
}

type Hr struct {
	Date civil.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 := HrDB{}
	err := rows.StructScan(&row)
	if err != nil {
		log(err.Error())
	}
	row.Hr.Date = civil.DateOf(row.Date)
	list = append(list, row.Hr)
}
return (list)

https://play.golang.org/p/3xH_JByx9q4

To obtain the ISO format date (2020-08-31) from the Hr.Date field, use the String() method. For example,

isoDate = row.Hr.Date.String()

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:

https://golang.org/search?q=iso+8601
https://godoc.org/?q=iso+8601


https://golang.org/search?q="2006-01-02"

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 :slight_smile: