Timestamp with Timezone in GoLang

Question

I make a HTTP call and unmarshal a createdTimestamp field from the larger json object:

CreatedTimestamp string `json:"createdTimestamp"`

An Example of what I receive from the HTTP call for the createdTimestamp would be: "2021-07-19T18:51:23".

It won't automatically convert it to a time.Time so the only type it would really accept is a string which was working until the type changed in Postgresql to timestamp with timezone < looks like this according to Postgresql db console >(yyyy-MM-dd HH:mm:ss.ffffff). where as before it was without timezone. I am able to implement a custom unmarshal method for the time.Time object and even format it so I can output something like 2021-07-19 18:51:23.+00 but this won't be accepted by postgres.

The exact error I see when trying to insert this (via GoLang, postgres driver) is: pq: invalid input syntax for type timestamp with time zone: "" This error comes after I try to execute an insert using db.Exec -> db being of type *sql.DB.

I'm doing this in Go , any help would be greatly appreciated!

Code to Unmarshal Json

type CustomTime struct {
    time.Time
}

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
    timeToInsertIntoDb := strings.Trim(string(b), "\"")
    if timeToInsertIntoDb == "null" {
        ct.Time = time.Time{}
        return
    }

    timeToInsertIntoDb = timeToInsertIntoDb + "Z"
    ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
    return
}

With formatting, it depends on the output but I do receive the output of whatever the format is. So if I do, CustomTime.Time.Format(2006-02-01 15:04:05.-07) I will get the output of 2021-07-19 18:51:23.+00

Though at this point, i'm not even sure about the exact format needed for Timestamp with Timezone, there isn't too much documentation on this for the Golang Postgres driver.

If there is any more information needed, please ask. I'm trying my best to organize this question.

Edit 1

---

As suggested, I tried to append on a 'Z' to the timestamp given from the http call. After doing a parse with time.RFC3339, I am given a time of 2021-07-19T18:51:23Z - this still failed (gave the same syntax error stated above). Tried it a few different ways with this parsing. With it formatted the way I stated above, and with it formatted with it's .String() method which would give 2021-07-20 18:51:23 +0000 UTC. Both failed with pq: invalid input syntax for type timestamp with time zone: ""

Code Changes during Unmarshal:

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
    timeToInsertIntoDb := strings.Trim(string(b), "\"")
    if timeToInsertIntoDb == "null" {
        ct.Time = time.Time{}
        return
    }

    timeToInsertIntoDb = timeToInsertIntoDb + "Z"
    ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
    return
}

---

Edit 2

Another thing to mention would be that I am using the "database/sql" package with the "github.com/lib/pq" as the Postgres driver for DB connection. Which is why the error is from pq. Just wanted to clarify cause I know others are using gorm. I can write to other tables, it's just this table having the timestamp with timezone Postgres Column I guess.

I am making the call with db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", obj.createdTimestamp.Time I've tried passing it along as a string (it's what I did before when it was working) but now this is where i'm at since people say it's better to pass a time.Time variable.

Answer 1

If you check the timeFormat ctlayout provided matches closely with the timeformat standard RFC3339

const ctLayout = "2006-01-02T15:04:05"
const RFC3339  = "2006-01-02T15:04:05Z07:00"

From this blogpost.

In RFC 3339, we can also know the time-zone from the format. It displayed in the “Z” syntax. “Z” means UTC+0. “Z” stands for Zulu timezone which is the same with GMT or UTC (https://stackoverflow.com/a/9706777/4075313). So if we put Z on the DateTime, it’s mean its timezone is UTC+0.

If we change the incoming time and append 'Z' at the end the time parser should be satisfied.

Here is a refactored code. You can find working code on playground.

        timeToInsertInDB := "2006-01-02T15:04:05" + "Z"

        testTime, err := time.Parse(time.RFC3339, timeToInsertInDB)
        if err != nil {
                fmt.Println(err) 
        }

Note :- More detailed example of timezones

2019-10-12T07:20:50.52Z      (UTC+0)
2019-10-12T07:20:50.52+00:00 (UTC+0)
2019-10-12T14:20:50.52+07:00 (UTC+7)
2019-10-12T03:20:50.52-04:00 (UTC-4)

The postgres driver expects time.Time. If the parsing is succesfull, driver should be able to insert into the database as well as unmarshall the response in the Model structs. Have a look at this example on playground.

edit 1

As per the OP's edit, I changed the drivers used I tried this with database/sql package and github.com/lib/pq but could not reproduce the issue. The insert and select worked completely fine. playground

As mentioned by Brits, this is most likely cause is a logic error in your code (but cannot confirm this as you have not shared the code).

Answer 2

Your question is difficult to answer because it does not currently contain sufficient information to allow me to replicate the issue.

As mentioned in the comments you can, and should, pass a time.Time when inserting timestamps into the database. Doing this means that the library takes care of passing the timestamp to the server in an appropriate format (and your code is likely to continue working if you move to, for example, SQL Server).

If you are passing a time.Time then the source of that time becomes irrelevant. This means your question does not really need to mention the conversion from JSON at all - just make sure you double check that this conversion is successful and you are passing the time.Time to Postgres.

The code below demonstrates this (and completes successfully for me with Postgres 13.1) - this code converts from JSON using the technique suggested by Shailesh Suryawanshi:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "time"
)

func main() {
    db, err := sql.Open("postgres", "DSN goes here")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create the table (would not normally do this here but this means the demo is self contained)
    _, err = db.Exec("create temporary table db(created_timestamp timestamp with time zone)")
    if err != nil {
        panic(err)
    }

    jsonTS := `2021-07-19T18:51:23`
    ts, err := time.Parse(time.RFC3339, jsonTS+"Z")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", ts)
    if err != nil {
        panic(err)
    }

    // Test retrieving the value
    rows, err := db.Query("select * from db")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var t time.Time
        err = rows.Scan(&t)
        if err != nil {
            panic(err)
        }
        fmt.Println(t)
    }

    fmt.Println("Complete")
}

I can replicate your issue by running db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", ""); the full error is:

panic: pq: invalid input syntax for type timestamp with time zone: ""

It may be possible that your issue is due to something in the Postgres configuration; running a modified version of the above code against your server will enable you to confirm if that is the case. However, based on the above, I believe the most likely cause is a logic error in your code (but cannot confirm this as you have not shared the code).

Note: the lib/pq readme states: "We recommend using pgx which is actively maintained."