How to parse Time in GOLANG work with SQL.

#go #sql #parseingdate #firstpost

In this post I will explain How to deal with the Local time zone By using Golang (time.Time) and SQL connection.

Issue :
I wrote an API for inserting data into SQL DB containing Some information with createdDate and updatedDate for logs.
But When I insert the Date from front-end then the date accepts 5 hours before the current date.

Code and output Before Solution:-

SQLConnection.go


func DbConn() (db *sql.DB) {

    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/new_lms")
    if err != nil {
        panic(err.Error())
    }
    return db
}

menuInsertDAO.go

//AddMenuService for insert menu
func AddMenuService(menu Menu) error {
    fmt.Println("add menu", menu.MenuId)
    db := helper.DbConn()
    menuDate := time.Now().UTC()
    fmt.Println("menuDate", menuDate, reflect.TypeOf(menuDate))
    menuData, err := db.Prepare("INSERT INTO mmenus(pkMenuId, menuName, routerLink, description, createdOn,createdBy) VALUES(?,?, ?,?,?,?)")
    if err != nil {
        panic(err.Error())

    }
    menuData.Exec(menu.MenuId, menu.MenuName, menu.RouterLink, menu.Description, menuDate, menu.CreatedBy)
    return nil
}

Output:

Backend O/P: menuDate 2021-01-04 11:45:12.6202467 +0530 IST m=+165.534486801 time.Time
SQLDB :

Now We Jump to the Solution:

1) What Happens in the back-end 🤔

It's just similar to the "wall clock".
for example, if we ask the date and time to Indian guys, he simply answers 4th-Jan-2021 03:08:12 and at the same time the other country guy's answer with different time like (New York: 4th-Jan-2021 04:40:21).
Here the location is only used for display purposes, it does not change the actual value of the time. By default, the location is going to be based on the local timezone of the machine.

2)Let's jump to SQL connection in Golang:

When we used SQL connection in the Golang we need to understand the timezone of SQL database.
MySQL used two timezone :
1)The timezone of the Local system, which is what the MySQL server is going to use.
2)The timezone connection itself.

SQL Connection:

When we use SQL connection then we able to parse value. in our case time.Now() returns the current time of the local system, and SQL Connection accepts this as global time.
This issue is resolved by parsing the date as location-wise.

db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/new_lms?parseTime=true&loc=Asia%2FCalcutta")

NOTE:- %2F : the '/' is replaced by %2F

menuInsertDAO.go

//AddMenuService for insert menu
func AddMenuService(menu Menu) error {
    fmt.Println("add menu", menu.MenuId)
    db := helper.DbConn()
    menuDate := time.Now().UTC()
    fmt.Println("menuDate", menuDate, reflect.TypeOf(menuDate))
    menuData, err := db.Prepare("INSERT INTO mmenus(pkMenuId, menuName, routerLink, description, createdOn,createdBy) VALUES(?,?, ?,?,?,?)")
    if err != nil {
        panic(err.Error())
        // return err
    }
    menuData.Exec(menu.MenuId, menu.MenuName, menu.RouterLink, menu.Description, menuDate, menu.CreatedBy)
    return nil
}

After parsing the time the output is:-
Back-end :
menuDate 2021-01-04 15:50:02.2094272 +0530 IST m=+58.177726101 time.Time
SQLDB:

This is my first post and I accept the other solution and suggestion.
Thank You for Reading.

Top comments (4)

Pratik Chaudhari • Jan 5 '21

Great post @rahulkarmore

Here's a tip for leveraging the 'syntax highlighting' in your posts:

You can specify the name of the language in your 'code block', like so:


 ```golang
    func DbConn() (db *sql.DB) {

        db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/new_lms")
        if err != nil {
            panic(err.Error())
        }
        return db
    }
```

Here's how your code will look after you specify the name of the programming language:

func DbConn() (db *sql.DB) {

    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/new_lms")
    if err != nil {
        panic(err.Error())
    }
    return db
}