How to convert date string to Time in to insert on mysql driver in Golang?-CodePudding

So, my problem is to convert a string date ("1941-09-09") to Time type.

resultInsertUser, err := tx.Exec(`INSERT INTO user 
(name, birthdate, password, email, document, phone, is_admin)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
    user.Name,
    timestamp,
    user.Password,
    user.Email,
    user.Document,
    user.Phone,
    user.IsAdmin)

I'm using Parse function like describe on official documentation and works but, when a using on insert query using MySQL driver doesn't work, error message below :(

"Incorrect datetime value: '1941-09-09 00:00:02' for column 'birthdate' at row 1"

I'm trying to use time.Now() instead variable for test and...works, how so?

Output the two variables:

now := time.Now()
timestamp, _ := time.Parse("2006-01-02", user.Birthdate)
fmt.Printf("now: %v\ntimestamp: %v",
    now,
    timestamp)

now: 2022-11-05 08:28:59.671061 -0300 -03 m= 3.423587668
timestamp: 1941-09-09 00:00:00  0000 UTC

Does someone help me with this?

CodePudding user response：

Your code is correct. The devil's in the detail (in mysql docs). Following are the formats as per mysql doc:

Data Type	Format	Range
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31
DATETIME	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD hh:mm:ss	1970-01-01 00:00:01 to 2038-01-19 03:14:07 (In UTC, i.e., Unix time)

As you might see DATETIME vs TIMESTAMP types, the formats are same but the ranges differ. The range of TIMESTAMP starts from year 1970.

Looking at your use case, the column birthdate's type is incorrect. Change it from TIMESTAMP -> DATETIME since the other way (converting 1941 as -0029 year relative to 1970) is not a good idea to implement in Go. Could lead to a lot of heavy-lifting.

Not sure why mysql logs the error saying the format is datetime if it altogether has a different type timestamp. Users bound to get confused.