DEV Community

loading...
Cover image for How to parse Time in GOLANG work with SQL.

How to parse Time in GOLANG work with SQL.

rahulkarmore profile image Rahul Karmore ・2 min read

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
}
Enter fullscreen mode Exit fullscreen mode
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
}
Enter fullscreen mode Exit fullscreen mode
Output:

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

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")
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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:
Alt Text

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

Discussion (4)

pic
Editor guide
Collapse
pratikgchaudhari profile image
Pratik Chaudhari

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
}
Enter fullscreen mode Exit fullscreen mode

Looks beautiful, doesn't it?

Collapse
rahulkarmore profile image
Rahul Karmore Author

its looks great, Thank you so much Pratik sir for your helping hand.

Collapse
ruteshrathod profile image
Rutesh Rathod

Informative post RahulπŸ‘

Collapse
rahulkarmore profile image
Rahul Karmore Author

thanks rutesh