Store date inside mysql database

we have date in “dd/mm/yyyy” format . mysql accept “yyyy-mm-dd” format.

how to convert “dd/mm/yyyy” for to “YYYY-mm-dd” format.

we have to insert into mysql database.

Can you show an example of how you’re inserting? Are you storing dates as one of MySQL’s string data types like CHAR or VARCHAR? If not, then the format of the date strings shouldn’t matter.

Just use SQL builder that converts a time.Time properly when used in the query…To be honest, I expect all basic SQL libraries to be able to deal with that…

don’t you store dates as time.Time ?
Isn’t the db driver able to use the right format for them ?

please show the code

1 Like

[{
“material_code”: 3702,
“Sitename”: “https://xyz.abc.com”,
“datatype”: “OUTSIDE”,
“XlOpendate”: “31-05-2022”
}]

Xlopendate format “DD/MM/YYYY”

type Excelwebsitelist struct {
Material_code int json:"material_code" validate:"required"
Sitename string json:"sitename" validate:"required"
datatype string json:"studenttype" validate:"required"
XlOpendate time.Time json:"xlopendate" validate:"required"
}

we are using gin framework.

we are want to insert data into mysql table ( XlOpendate field of type date )

Please use backticks to make your code looking proper, like this:

```go
// code goes here
```

Will render like:

// code goes here

So how are you querying the database? The XlOpendate field in the struct is already time.Time, unless your DB query is written weirdly, I expect that to just work.

it is working now

		mydate, err := time.Parse("02-01-2006", data.XlOpendate) //dd/mm/yyyy
		if err != nil {
			err := errors.New("Parsing error" + err.Error())
			return err
		}
		str_date := mydate.Format("2006-02-01") // formated in YYYY/MM/DD
		date, err := time.Parse("2006-02-01", str_date) // transafer to Date object 
		if err != nil {
			err := errors.New("Parsing error" + err.Error())
			return err
		}

It is solved . This is correct method .

I still do not see a DB query here. What ever you do, it works around the actual proble…

At the end, mydate and date will actually be equal! Just try it, mydate == date will hold forever!

I am still confused .

if I take Xlopendate field as a Time.time. I am getting this error
{
“Binding exception”: “parsing time “\“31-04-2022\”” as “\“2006-01-02T15:04:05Z07:00\””: cannot parse “4-2022\”” as “2006"”
}

If I use string I have to convert .

So the XlOpendate is not a time.Time? Then the info you gave earlier was wrong and misleading.

Anyway, from your last snippet you can just use mydate without any further back and forth converting.

1 Like

Hi,

sorry but you’re making some confusion.
Can you post the JSON and the exact struct you’re using?

then you should also show the code used to insert into the DB.
The workflow should be

  • unmarshal the JSON data into a struct
  • store the struct in the DB
1 Like
[{
	"Website_code": 3702,
	"Sitename": "https://xyz.abc.com",
	"datatype": "OUTSIDE",
	"XlOpendate": "31-05-2022",
	"Remarks":"dummy data"
 }]

type Excelwebsitelist struct {
	Website_code  int    `json:"website_code" validate:"required"`
	Sitename    string `json:"sitename" validate:"required"`
	Datatype string `json:"datatype" validate:"required"`
	XlOpendate  string `json:"xlopendate" validate:"required"`
	Remarks     string `json:"remarks" validate:"required"`

	Opendate time.Time
}

func Uploaddata(_in []req.Excelwebsitelist) error {

	tx, err := Mydb.Beginx()
	if err != nil {
		err := errors.New("database error" + err.Error())
		return err
	}
	defer func(tx *sqlx.Tx) {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}(tx)

	for index, data := range _in {
		mydate, err := time.Parse("02-01-2006", data.XlOpendate) //dd/mm/yyyy
		if err != nil {
			err := errors.New("Parsing error" + err.Error())
			return err
		}
		str_date := mydate.Format(Mysqldateformat)         // formated in YYYY/MM/DD
		date, err := time.Parse(Mysqldateformat, str_date) // transafer to Date object
		if err != nil {
			err := errors.New("Parsing error" + err.Error())
			return err
		}
		_in[index].Opendate = date
	}

	_InsertQuery := `
			insert into website (
			  website_code,sitename,datatype,opendate ,remarks
			) values (:website_code,:sitename,:datatype,:opendate ,:remarks) 
			ON DUPLICATE KEY UPDATE  
			datatype =VALUES(datatype),opendate = VALUES(opendate),remarks = values(remarks)`
		

	_, err = tx.NamedExec(_InsertQuery, _in)
	if err != nil {
		return err
	}
	return nil

}

from user we have taken “datetime” in string and runtime we have converted to TIME.time , and saved in database .

which I think wrong way of programming .

I see you’re doing a double conversion and I don’t understand why

  1. mydate, err := time.Parse("02-01-2006", data.XlOpendate): string → Time
  2. str_date := mydate.Format(Mysqldateformat): Time → string
  3. date, err := time.Parse(Mysqldateformat, str_date): string → Time

why don’t use mydate directly?

Tip: why don’t define the Field as Time and implement UnmarshalJSON() for the struct ?

1 Like
  1. mydate, err := time.Parse("02-01-2006", data.XlOpendate): string → Time
    Answer : converting string into Time with "DD/MM/YYYY
  2. str_date := mydate.Format(Mysqldateformat): Time → string
    Answer : Time to string
  3. date, err := time.Parse(Mysqldateformat, str_date): string → Time
    Answer : converting to mysql date format ( YYYY/MM/DD ).

isn’t the DB driver able to convert a Time in the correct format without passing to string?

which driver are you using ?

github.com/go-sql-driver/mysql

isn’t the driver able to work directly on Time variables?