MySQL UTC server error

[malcolm-davis]

Describe the bug
On a server set to the UTC timezone as the location, the format (with RFC3339) will output "Z", rather than "-00:00".

For both datetime and timestamp field types, MySQL will report an "incorrect datetime value".

The "Z"output is a known golang feature.

Steps to Reproduce
Table with
createdAt DATETIME DEFAULT NULL
'updatedAt' TIMESTAMP DEFAULT NULL

Go struct with
CreatedAt strfmt.DateTime json:"createdAt,omitempty" db:"createdAt"
UpdatedAt *strfmt.DateTime json:"updatedAt,omitempty" db:"updatedAt"

Try using the following in sql code
nowFmt := strfmt.DateTime(time.Now())
testCommentInsert := &mod.TestComment {
Comment: StringPtr("Need help"),
CreatedAt: nowFmt,
UpdatedAt: &nowFmt,
}

The issue does not occur when using time.Time
Switching from strfmt.DateTime to time.Time fixes the problem
CreatedAt time.Time json:"createdAt,omitempty" db:"createdAt"
UpdatedAt *time.Time json:"updatedAt,omitempty" db:"updatedAt"

Expected behavior
strfmt.DateTime to properly handle UTC with go.

Environment
Unix base MySQL running UTC.

[fredbi]

Hello.

The Z is part of the RFC3339, it is not a golang feature.
The go driver for mysql is documented for not handling well timezones a the client.
It only works with time.Time because the driver intercepts this type to apply a special formatting.
You may give a shot with the Loc parameter, but for UTC, it's probably not going to change much.

What you can do with the strfmt library is changed some global variables that affect time formatting.
MashalFormat should do the job. My hunch is that a format like defined by ISO8601LocalTime should do the trick.
You may define the format that suits you. Unfortunately, this is a global, so all instances of this time are affected.

Hope this helps.

[malcolm-davis]

I see. Go is using the 'Z' protocol in RFC3339. The layout string Z0700 or Z07:00 instructs Go to print "Z" for the UTC zone, instead of "+0000" or "+00:00".

Essentially, RFC3339 provides inconsistent results, and MySQL doesn't like it, requiring conversion. I wouldn't expect any database to be RFC3339-aware by default.

You might be correct that the driver intercepts time.Time and does some formatting.

I already have special code generation logic to handle other database-related items. I may perform additional processing after further testing.

I'm transitioning from datetime to timestamp for create & update related features. From what I can tell, OpenAPI does not handle the timestamp feature.

Thanks for the clarification.

[fredbi]

Feel free to reopen if you have further issues or questions