解析PostgreSQL TIMESTAMP类型的值时出现问题 [英] Problem parsing values of PostgreSQL TIMESTAMP type

问题描述

在PostgreSQL中，我有一个名为 surveys 的表.

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
);

您只能看到 SURVEY_ID 和 SURVEY_NAME 列是 NOT NULL .

在Go中，我想通过 POST 请求在该表中创建新条目.我发送这样的JSON对象:

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.")
}

推荐答案

该错误已经说明了错误所在:

The error already says what is wrong:

解析时间""2019-01-01 00:00:00"如"2006-01-02T15:04:05Z07:00":无法解析"00:00:00"为"T"

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

您传递的是" 2019-01-01 00:00:00" ，而它期望使用不同的时间格式，即 UnmarshalJSON的默认).

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

要解决此问题，您要么希望以期望的格式传递时间"2019-01-01T00:00:00Z00:00" ，要么定义自己的类型 CustomTime

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
}

小心，您可能还需要实现 Valuer 和 Scanner 接口，以便在数据库中进行解析和解析，如下所示:/p>

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
}

进入游乐场示例.

这篇关于解析PostgreSQL TIMESTAMP类型的值时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！