MYSQL timestamps in Go

August 27, 2017

Simple but handy

Formatting time in Go is a very interesting topic but let’s focus on a particular case: a popular TIMESTAMP format used in MySQL: ‘0000-00-00 00:00:00’. It turns out that if you want to use it in Go, you have to format time yourself. Luckily it’s a very easy job in Go.

Let’s take current time:

t := time.Now()
fmt.Println(t)

This will result in displaying time in the following format:

2017-08-27 02:33:32.991423555 +0200 CEST m=+0.000180761

Adding just one extra line does the job for us:

t := time.Now()
ts := t.Format("2006-01-02 15:04:05")
fmt.Println(ts)

Here we go:

2017-08-27 02:35:13

but let’s make it more useful by adding a structure that holds Offset that will be added to the current timestamp and the time unit of the offset. This gives us a flexibility of creating timestamps of any point in time:

type FromNow struct {
    Offset   int
    TimeUnit time.Duration
}

The whole conversion will happen in the implementation of Stringer interface:

func (ts FromNow) String() string {
    t := time.Now().Add(ts.TimeUnit * time.Duration(ts.Offset))
    timeStamp := t.Format("2006-01-02 15:04:05")

    return fmt.Sprintf("%v", timeStamp)
}

The full package looks like this and can be found on github:

package timestamp

import (
    "fmt"
    "time"
)

// FromNow Offset is a number that will multiply TimUnit
// and the result will be added to the current time
type FromNow struct {
    Offset   int
    TimeUnit time.Duration
}

func (ts FromNow) String() string {
    t := time.Now().Add(ts.TimeUnit * time.Duration(ts.Offset))
    timeStamp := t.Format("2006-01-02 15:04:05")

    return fmt.Sprintf("%v", timeStamp)
}

And some sample calls:

now := timestamp.FromNow{}
fmt.Println("current timestamp: ", now)

Timestamp of 30 min from now

expiresAt := timestamp.FromNow{
	Offset:   30,
	TimeUnit: time.Minute,
}
fmt.Println("expires at: ", expiresAt)

Timestamp of 2 hours from now

reminder := timestamp.FromNow{
	Offset:   2,
	TimeUnit: time.Hour,
}
//let's put the string into a sample SQL query
exampleQuery := "UPDATE reminders SET when = " + reminder.String()
exampleQuery += " WHERE reminders.id = 1;"

fmt.Println("example SQL query: ", exampleQuery)

Timestamp of 15 min ago

expiredAt := timestamp.FromNow{-15, time.Minute}
fmt.Println("expired at: ", expiredAt)

As you can see Golang is very straight forward to read and simple tasks are simple :)