Pg: Set or update TIMESTAMP WITHOUT TIMEZONE fails because of wrong value formating.

[RepentantGopher]

Hi.
I have model with field type of time.Time which refers to column in postgres database type of TIMESTAMP WITHOUT TIMEZONE.
While reading value from this field works fine, insert or update operations fails with error like:

ERROR #42804 column \"created_at\" is of type timestamp without time zone but expression is of type text

On model bulk update library generates following sql query:

    sms_recipient AS "sms_recipient_model"
SET 
    "created_at" = _data."created_at", 
    "processed_at" = _data."processed_at"
FROM (
    VALUES (
        20, 
        '2017-08-07 14:37:57.375559663+00:00:00',
        '2017-08-07 14:37:57.375559663+00:00:00'
    )   
) AS _data(
    "id", 
    "created_at", 
    "processed_at"
) WHERE 
    "sms_recipient_model"."id" = _data."id";

which definitely broken, but can be simply fixed by adding convertion to timestamp.
Working sql query looking like this:

    sms_recipient AS "sms_recipient_model"
SET 
    "created_at" = _data."created_at", 
    "processed_at" = _data."processed_at"
FROM (
    VALUES (
        20, 
        '2017-08-07 14:37:57.375559663+00:00:00'::timestamp, 
        '2017-08-07 14:37:57.375559663+00:00:00'::timestamp
    )   
) AS _data(
    "id", 
    "created_at", 
    "processed_at"
) WHERE 
    "sms_recipient_model"."id" = _data."id";

In source code this bug could be simply fixed by adding this convertion to AppendTime function
https://github.com/go-pg/pg/blob/master/types/time.go#L34
like this

func AppendTime(b []byte, tm time.Time, quote int) []byte {
    if quote == 1 {
        b = append(b, '\'')
    }
    b = tm.AppendFormat(b, timestamptzFormat)
    if quote == 1 {
        b = append(b, '\'')
    }
    b = append(b, []byte("::timestamp")...)
    return b
}

Should i make pull request with these changes or i don't understand it right?

[vmihailenco]

I wonder if it works if you change type from timestamp to timestamptz?

[RepentantGopher]

Yes it works.

    sms_recipient AS "sms_recipient_model"
SET 
    "created_at" = _data."created_at", 
    "processed_at" = _data."processed_at"
FROM (
    VALUES (
        20, '2017-08-07 14:37:57.375559663+00:00:00'::timestamptz, '2017-08-07 14:37:57.375559663+00:00:00'::timestamptz
    )

) AS _data(
    "id", 
    "created_at", 
    "processed_at"
) WHERE 
    "sms_recipient_model"."id" = _data."id";

[vmihailenco]

I am tempted to say that it is working as intented and you should use timestamptz, because it is better in every aspect.

Changing AppendTime to use timestamp will break all the code that uses timestamptz.

[RepentantGopher]

I am tempted to say that it is working as intented and you should use timestamptz, because it is better in every aspect.

I can't agree with you because in last few years timezones in my country was changed very often, so in my opinion it's better to store time in UTC without tz to avoid headache in future:)

I'll test my changes with columns of different time types and if it will be ok ill create pull request.

[vmihailenco]

See http://justatheory.com/computers/databases/postgresql/use-timestamptz.html . It is not obvious from the name, but timestamptz stores time in UTC and converts it to whatever your time zone setting is.