How to save time in the database in Go when using GORM and Postgresql?

Question

I'm currently parsing a time string and saving it to the db (Postgresql):

event.Time, _ := time.Parse("3:04 PM", "9:00 PM")
// value of event.Time now is: 0000-01-01 21:00:00 +0000 UTC
db.Create(&event)

It's giving me this error: pq: R:"DateTimeParseError" S:"ERROR" C:"22008" M:"date/time field value out of range: \"0000-01-01T21:00:00Z\"" F:"datetime.c" L:"3540"

event.Time⁠⁠⁠⁠'s type is time.Time.

I also tried setting event.Time's type to string and using time data type in postgresql:

type Event struct {
  Time string `gorm:"type:time
}

But now I'm getting an error when fetching records in the db:

sql: Scan error on column index 4: unsupported driver -> Scan pair: time.Time -> *string

That's because Go time.Time is actually a date. Currently I don't see a way to use PostgreSQL Time properly. If you aren't querying by it, I would suggest to use a simple string. Otherwise, store it as a full date. — Alexey Soshin
– Alexey Soshin, Commented Sep 14, 2016 at 17:36
@AlexeySoshin I already tried to store it as a full date but it's giving me this error: pq: R:"DateTimeParseError" S:"ERROR" C:"22008" M:"date/time field value out of range: \"0000-01-01T21:00:00Z\"" F:"datetime.c" L:"3540" — John Kenn
– John Kenn, Commented Sep 15, 2016 at 0:47
As a workaround I'd suggest adding some dummy date like 2099-01-01 and you could then store your time and query on it properly having this in mind. — Kamil Gosciminski
– Kamil Gosciminski, Commented Sep 15, 2016 at 20:09

Alexey Soshin · Accepted Answer · 2016-09-15 20:05:48Z

7

Investigated this issue further. Currently, there's no support in GORM for any Date/Time types except timestamp with time zone

See this part of code from dialect_postgres.go:

case reflect.Struct:
   if _, ok := dataValue.Interface().(time.Time); ok {
      sqlType = "timestamp with time zone"
}

So basically I see two options for you:

Either use varchar(10) in DB, and string in Go, an simply save it as "9:00 PM" (where 10 is some number that suits you)

Or use timestamp with time zone in DB, time.Time in Go, and format your date part as a constant date, 01/01/1970, for example:

time.Parse("2006-01-02 3:04PM", "1970-01-01 9:00PM")

In that case you'll have to omit the date part in your presentation, but if you plan to select by date range, that could work better for you.

answered Sep 15, 2016 at 20:05

Alexey Soshin

17.9k2 gold badges37 silver badges42 bronze badges

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

Comments

dimus · Accepted Answer · 2022-01-08 10:50:02Z

2

You can set an arbitrary database-specific type with Gorm using sql tag

type Event struct {
  Time time.Time `sql:"type:timestamp without time zone"`
}

answered Jan 8, 2022 at 10:50

dimus

9,50011 gold badges50 silver badges57 bronze badges

1 Comment

chmike Over a year ago

Unfortunately it doesn't work for me with gorm v1.23.5 and postgres 14.

Bill Zelenko · Accepted Answer · 2019-08-12 17:37:32Z

0

When updating the DATETIME field in SQL, the Go string must be in this format: time.Now().Format(time.RFC3339).

answered Aug 12, 2019 at 17:37

Bill Zelenko

2,8881 gold badge22 silver badges27 bronze badges

Comments

Belayer · Accepted Answer · 2019-08-13 03:39:18Z

0

From Postgres perspective the error stems from there being no year 0000. If you don't the date you may just be able to add 1 year to the converted timestamp giving '0001-01-01T21:00:00+00' which is a valid Postgres timestamp.

select '0000-01-01T21:00:00+00'::timestamptz at time zone 'UTC' 
ERROR:  date/time field value out of range: "0000-01-01T21:00:00+00"

Gives he same error. And just as a demonstration 1 day before 0001-01-01 gives:

select '0001-01-01T21:00:00+00'::timestamptz at time zone 'UTC' - interval '1 day' "day_before_1/1/1";
--day_before_1/1/1
--0001-12-31 21:00:00 BC

answered Aug 13, 2019 at 3:39

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to save time in the database in Go when using GORM and Postgresql?

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related