Beego ORM in the time zone problem

This is a creation in Article, where the information may have evolved or changed.

First look at the simplified code, the following list is only the main function

func main() {    t := "2017-01-19 00:00:00"    o := orm.NewOrm()    qb, _ := orm.NewQueryBuilder("mysql")    sql := qb.Select("COUNT(*)").From("test").Where("create_time > ?").String()    o.Raw(sql, t).Exec()    o.QueryTable("test").Filter("create_time__gt", t).Count()}

So it feels like two SQL should be the same:

[ORM] - 2017-01-19 19:28:02 - [Queries/default] - [  OK /     db.Exec /     1.2ms] - [SELECT COUNT(*) FROM test WHERE create_time > ?] - `2017-01-19 00:00:00`[ORM] - 2017-01-19 19:28:02 - [Queries/default] - [  OK / db.QueryRow /     2.3ms] - [SELECT COUNT(*) FROM `test` T0 WHERE T0.`create_time` > ? ] - `2017-01-19 00:00:00`

I am testing OK on this machine, but in another environment SQL is like this:

[ORM] - 2017-01-19 11:30:43 - [Queries/default] - [  OK /     db.Exec /     1.2ms] - [SELECT COUNT(*) FROM test WHERE create_time > ?] - `2017-01-19 00:00:00`[ORM] - 2017-01-19 11:30:43 - [Queries/default] - [  OK / db.QueryRow /     1.2ms] - [SELECT COUNT(*) FROM `test` T0 WHERE T0.`create_time` > ? ] - `2017-01-19 08:00:00`

A difference of 8 hours, the first time to think about time zone problems, to have a problem of the environment to see if so.

Then look at the next Beego ORM Code, which lists the key sections below.
1. orm/db_utils.go the getFlatParams()
This function is a Filter() critical part of parsing the generated SQL, and if the Filter() first parameter type is date or datetime, the second argument type is string to parse the string into time. Time Type
In the case above, len(v) = 19 time.ParseInLocation(formatDateTime, s, DefaultTimeLoc) because the problematic environment is the UTC timezone, this function parses the string 2017-01-19 00:00:00 into time. 2017-01-19 00:00:00 +0000 UTCtime (variable T, but actually the East eight zone, the correct t should be 2017-01-19 00:00:00 +0800 CST ).

Func Getflatparams (fi *fieldinfo, args []interface{}, TZ *time. Location) (params []interface{}) {... switch kind {case reflect. String:v: = val.                String () If fi = nil {if Fi.fieldtype = = Typedatefield | | fi.fieldtype = = Typedatetimefield { var t time.  Time Var err error if Len (v) >= {s: = v[:19] t, Err = time.  Parseinlocation (FormatDateTime, S, Defaulttimeloc)} else {s: = v if Len (v) > {s = v[:10]} t, err = time. Parseinlocation (FormatDate, S, TZ)} if Err = = Nil {if Fi.fieldtype = = T Ypedatefield {v = t.in (TZ). Format (FormatDate)} else {v = t.in (TZ).   Format (FormatDateTime)}}}     arg = v ...} 

2. t.In(tz).Format(formatDateTime) format T again as a string, the parameter tz is key, it is assigned in the code below. Because MySQL is set to the East eight zone, it will be set to Al. TZ is East Eight, that t.In(tz).Format(formatDateTime) is, the middle TZ is the East eight, resulting in Format the return of the string is 2017-01-19 08:00:00 , so there are the above two SQL different problems.

func detectTZ(al *alias) {    // orm timezone system match database    // default use Local    al.TZ = time.Local    ......     switch al.Driver {    case DRMySQL:        row := al.DB.QueryRow("SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP)")        var tz string        row.Scan(&tz)        if len(tz) >= 8 {            if tz[0] != '-' {                tz = "+" + tz            }            t, err := time.Parse("-07:00:00", tz)            if err == nil {                al.TZ = t.Location()            } else {                DebugLog.Printf("Detect DB timezone: %s %s\n", tz, err.Error())            }        }......