Formatting nullable datatime fetched from DB


(BentCoder) #1

Hi,

I am using a custom scanner to handle nullable DATETIME fields but there is a small pending enhancement. How can I get the scanner to return "" or 0000-00-00 00:00:00 instead of 0001-01-01 00:00:00 if the date coming from DB is empty?

Thanks

Current terminal output
{0001-01-01 00:00:00 +0000 UTC false}

Current JSON output

{
   "deleted_at": {
       "Time": "0001-01-01T00:00:00Z",
       "Valid": false
   }
}

Expected JSON output

{
   "deleted_at": {
       "Time": "", // or 0000-00-00 00:00:00
       "Valid": false
   }
}

USAGE

DeletedAt database.NullTime `json:"deleted_at"`
package database

import (
	"fmt"
	"time"
)

type NullTime sql.NullTime

func (nt *NullTime) Scan(value interface{}) error {
	if value == nil {
		nt.Time, nt.Valid = time.Time{}, false

		return nil
	}

	var err error

	switch v := value.(type) {
	case time.Time:
		nt.Time, nt.Valid = v, true

		return nil
	case string:
		nt.Time, err = time.Parse("2006-01-02 15:04:05", v)
		nt.Valid = err == nil

		return nil
	case []byte:
		nt.Time, err = time.Parse("2006-01-02 15:04:05", string(v))
		nt.Valid = err == nil

		return nil
	default:
		nt.Time, nt.Valid = time.Time{}, false

		return fmt.Errorf("unable to scan %T into type *time.Time", value)
	}
}

(Christophe Meessen) #2

“0000-00-00 00:00:00” is an invalid time because the date “0000-00-00” does not exist. This is why the default value of time is “0001-01-01”.

If you want to have another time value format in the JSON output, you have to define another JSON Marshaller.