Problem parsing values of PostgreSQL TIMESTAMP type

Question

In PostgreSQL, I have table called surveys.

CREATE TABLE SURVEYS(
  SURVEY_ID UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  SURVEY_NAME VARCHAR NOT NULL,
  SURVEY_DESCRIPTION TEXT,
  START_PERIOD TIMESTAMP,
  END_PERIOD TIMESTAMP
);

As you can see only SURVEY_ID and SURVEY_NAME columns are NOT NULL.

In Go, I want to create new entry in that table by POST request. I send JSON object like this:

{
    "survey_name": "NAME",
    "survey_description": "DESCRIPTION",
    "start_period": "2019-01-01 00:00:00",
    "end_period": "2019-02-28 23:59:59"
}

Unfortunatly it raise strange ERROR:

parsing time ""2019-01-01 00:00:00"" as ""2006-01-02T15:04:05Z07:00"": cannot parse " 00:00:00"" as "T"

Where I make mistake and how to fix my problem?

models/surveys.go:

import (
    "database/sql"
    "time"
)

type NullTime struct {
    time.Time
    Valid bool
}

type Survey struct {
    ID int `json:"survey_id"`
    Name string `json:"survey_name"`
    Description sql.NullString `json:"survey_description"`
    StartPeriod NullTime `json:"start_period"`
    EndPeriod NullTime `json:"end_period"`
}

controllers/surveys.go:

var CreateSurvey = func(responseWriter http.ResponseWriter, request *http.Request) {
    // Initialize variables.
    survey := models.Survey{}
    var err error

    // The decoder introduces its own buffering and may read data from argument beyond the JSON values requested.
    err = json.NewDecoder(request.Body).Decode(&survey)
    if err != nil {
        log.Println(err)
        utils.ResponseWithError(responseWriter, http.StatusInternalServerError, err.Error())
        return
    }
    defer request.Body.Close()

    // Execute INSERT SQL statement.
    _, err = database.DB.Exec("INSERT INTO surveys (survey_name, survey_description, start_period, end_period) VALUES ($1, $2, $3, $4);", survey.Name, survey.Description, survey.StartPeriod, survey.EndPeriod)

    // Shape the response depending on the result of the previous command.
    if err != nil {
        log.Println(err)
        utils.ResponseWithError(responseWriter, http.StatusInternalServerError, err.Error())
        return
    }
    utils.ResponseWithSuccess(responseWriter, http.StatusCreated, "The new entry successfully created.")
}

time.Time's UnmarshalJSON uses RFC3339 format to parse the timestamp string, so you need to change the format of the timestamps that you're sending in the json accordingly, or you need to implement your own UnmarshalJSON on your NullTime type. — mkopriva
– mkopriva, Commented Feb 28, 2019 at 9:08

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

The error already says what is wrong:

parsing time ""2019-01-01 00:00:00"" as ""2006-01-02T15:04:05Z07:00"": cannot parse " 00:00:00"" as "T"

You are passing "2019-01-01 00:00:00" while it expects a different time format, namely RFC3339 (UnmarshalJSON's default).

To solve this, you either want to pass the time in the expected format "2019-01-01T00:00:00Z00:00" or define your own type CustomTime like this:

const timeFormat = "2006-01-02 15:04:05"

type CustomTime time.Time

func (ct *CustomTime) UnmarshalJSON(data []byte) error {
    newTime, err := time.Parse(timeFormat, strings.Trim(string(data), "\""))
    if err != nil {
        return err
    }

    *ct = CustomTime(newTime)
    return nil
}

func (ct *CustomTime) MarshalJSON() ([]byte, error) {
    return []byte(fmt.Sprintf("%q", time.Time(*ct).Format(timeFormat))), nil
}

Careful, you might also need to implement the Valuer and the Scanner interfaces for the time to be parsed in and out of the database, something like the following:

func (ct CustomTime) Value() (driver.Value, error) {
    return time.Time(ct), nil
}

func (ct *CustomTime) Scan(src interface{}) error {
    if val, ok := src.(time.Time); ok {
        *ct = CustomTime(val)
    } else {
        return errors.New("time Scanner passed a non-time object")
    }

    return nil
}

Go Playground example.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 28, 2019 at 9:07

ifnotak

4,8103 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

mkopriva Over a year ago

@NurzhanNogerbek to be able to save/retrieve custom struct types like your NullTime you need to have them implement the Scanner and Valuer interfaces. As an example just look at the database/sql documentation, there you'll find multiple custom NullXxx struct types, including NullString, and they all implement the two interfaces. You need to do that too to fix the sql: unsupported type error.

mkopriva Over a year ago

@NurzhanNogerbek "why timeFormat variable has such value 2006-01-02 15:04:05?" It's referred to as the "reference time". The reference time is used by the time package to parse the layout value, based on which it will then Parse/Format the time.Time values. If you go to the documentation and search (CTRL+F) for "reference time" you'll find enough information to help you better understand it.

mkopriva Over a year ago

@NurzhanNogerbek it depends on how you implemented the Value() (driver.Value, error) method.

mkopriva Over a year ago

@NurzhanNogerbek for example take a look at how NullString does it, if it's not valid it returns nil which gets saved as NULL in the db. So you have to check if your CustomTime is valid, if it is not return nil, if it is return what you're returning now.

mkopriva Over a year ago

@NurzhanNogerbek Valid is not part of the CustomTime as suggested by Abdullah, it was a field declared on your original type NullTime, and I recommend you stick to that approach. Use all of abdulla's suggestions but instead of CustomTime use NullTime, i.e. implement those methods on NullTime and drop CustomTime altogether. And just to clarify, the naming is not the problem here, the structure of the type is, so if you want to call it CustomTime go ahead, just change it to the correct structure. Example: play.golang.com/p/JU7xUuQTCO1

|

Collectives™ on Stack Overflow

Problem parsing values of PostgreSQL TIMESTAMP type

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related