CODEWITHSUNDEEP
postgresqlgo
Escher
Escher

Reputation: 5786

How to get timestamp value out of Postgresql without time zone?

I have a table with timestamp TIMESTAMP, data TEXT columns. I have a failing test because I can't get a timestamp value out of postgresql without time zone annotation. Here's an abridged version of what I've done in my Go application:

type Datapoint struct {
    Timestamp  string
    Data       sql.NullString
}
var testData = Datapoint{Timestamp:'2018-12-31 00:00:00', Data:'test'}

db.Exec("CREATE TABLE mytable (id SERIAL, timestamp TIMESTAMP, data TEXT);")
db.Exec("INSERT INTO mytable(timestamp, data) VALUES ($1, $2);", testData.Timestamp, testData.Data)
datapoints, err = db.Exec("SELECT timestamp::TIMESTAMP WITHOUT TIME ZONE, data FROM mytable;")

This trouble is that this query (after about 20 lines of error checking and row.Scan; golang's a bit verbose like that...) gives me:

expected 2018-12-31 00:00:00, received 2018-12-31T00:00:00Z

I requested without timezone (and the query succeeds in psql), so why am I getting the extra T and Z in the string?

Upvotes: 4

Views: 12312

Answers (2)

Peter
Peter

Reputation: 31741

Scan into a value of time.Time instead of string, then you can format the time as desired.

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"
)

type Datapoint struct {
    Timestamp time.Time
    Data      sql.NullString
}

func main() {
    var db *sql.DB

    var dp Datapoint    
    err := db.QueryRow("SELECT timestamp, data FROM mytable").Scan(
        &dp.Timestamp, &dp.Data,
    )

    switch {
    case err == sql.ErrNoRows:
        log.Fatal("No rows")
    case err != nil:
        log.Fatal(err)
    default:
        fmt.Println(dp.Timestamp.Format("2006-01-02 15:04:05"))
    }

}

Upvotes: 6

tmcnicol
tmcnicol

Reputation: 566

What you are receiving is an ISO 8601 representation of time.

T is the time designator that precedes the time components of the representation.

Z is used to represent that it is in UTC time, with Z representing zero offset.

In a way you are getting something without a timezone but it can be confusing, especially as you haven't localised your time at any point. I would suggest you consider using ISO times, or you could convert your time to a string like this 

s := fmt.Sprintf("%d-%02d-%02d %02d:%02d:%02d\n",
    t.Year(), t.Month(), t.Day(),
    t.Hour(), t.Minute(), t.Second())

Upvotes: 0

Related Questions