Golang time package with MySQL
Recently I had been working on my hobby project. In that, I came to a problem, How can I delete older cookies from my databases. My solution was to add a separate service which sole purpose is doing database rated routines. Clean-up,backup etc.
To implement that, I had to examine golang time package. Here in this short post,I will list and explain things I used and from this package and how useful they are to deal with time based problems in projects.
- Golang saves time in UTC
First problem I got ,when I save a record in database, current time is changed into UTC time. I have used location.In() function to get my local time.
mytime := time.Now().In("Asia/Colombo")
This showed me right local time. And same was used as a parameter to insert into database, changed it into UTC . Initially I thought its my mistake. Then I was looking for a solution , I found this useful thread. Which is similar to my nature. As nothingmuch explained :
- Default timezone setting in github.com/go-sql-driver/mysql is set to UTC. To override it, we would need to edit and set our appropriate location. Which is not a good idea for an average developer. The best thing he says is this.
This greatly simplifies anything having to do with date math further down the line, and doesn’t make assumptions about the time zone of the person viewing the data if you just convert it from UTC to local when displaying the value.
2. How to get datetime data
As I have mention earlier, I save login tokens in a table with datetime column. Then I needed to get this column and compare current time with it and perform tasks based on that comparison.
rows, err := db.Query(“select time_based_column from login_token”)I can get data from this, but its shown as 01:01:0001 01:01:01. You know what comes to your mind.
With the help of stackoverflow, I found the solution for this too. By default this is how data is extracted by go-sql-driver/mysql driver. To overide this , we can pass parseTime=trueit as a parameter to your connection open method.
db, err := sql.Open("mysql", "root:@/?parseTime=true")This solution fixed my problem. It seems this is kind of common for most of database drivers used. I encountered similar issue with Apache-NiFi and PostgreSQL connection.
jdbc:postgresql://123.456.789.12:5432/test?stringtype=unspecified
3. Compare two time parameters
After getting time column properly, next step was to compare now time and saved values in database. To do this operation, I had to examine golang-time package.
There I found time.Sub(time_to_comapre), and returns int64 value (Duration). So to check current time and save time difference,
t := time.Now()
utc := t.In(time.UTC) // current UTC timetimeDiff := utc.Sub(lastUpdated).Minutes() // lastUpdated -> taken from database.
// timeDiff -> hold subtracted value in minutes.
golang-time package has a lots of other features, they are well documented.
Thats yet for today, this is really a tiny description about a very large package. I may have to use other time package function, if so I will post a second part.
Happy Coding and Stay Safe…
