PostgreSQL Timestamp with timezone #7149

ggoosen · 2024-08-09T02:59:16Z

ggoosen
Aug 9, 2024

When defining types with Time.time column types, the table is created with Timestamp with timezone.

It seems that the framework defaults Date?time values to 1-Jan-0000 instead of being able to be NULL.

The gorm standard model uses a "DeletedAt" type for the deleted at field which seems to work an allow nullable sql.

I ended up doing a workaround by defining a new type called NullableTime, my question is, am i doing something wrong? Seems like a normal requirement to allow null values for dates?

type User struct {
	gorm.Model
	// Username of the user
	// @example johndoe
	Username string `json:"username" example:"johndoe"`
	// First name of the user
	// @example John
	FirstName string `json:"first_name" example:"John"`
	// Last name of the user
	// @example Doe
	LastName string `json:"last_name" example:"Doe"`
	// Email address of the user
	// @example john.doe@example.com
	Email string `json:"email" example:"john.doe@example.com"`
	// Password of the user (hashed)
	// @swaggerignore
	Password string `json:"-" swaggerignore:"true"` // "-" excludes from JSON, swaggerignore excludes from Swagger docs
	// Role of the user
	// @example admin
	Role string `json:"role" example:"admin"`
	// Reset token for password recovery
	ResetToken string `json:"-"`
	// Expiration time for the reset token
	ResetTokenExpires NullableTime `json:"-"`
	// Flag indicating if the user is required to change password
	// @example true
	RequirePasswordChange bool `json:"require_password_change" example:"true"`
	// Timestamp of the last password change
	PasswordLastChanged NullableTime `json:"password_last_changed"`
	// Number of failed login attempts
	// @example 3
	FailedLoginAttempts int `json:"failed_login_attempts" example:"3"`
	// Flag indicating if the account is locked
	// @example true
	AccountLocked bool `json:"account_locked" example:"true"`
	// Timestamp until when the account is locked
	AccountLockedUntil NullableTime `json:"account_locked_until"`
}

package models

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"time"
)

// NullableTime wraps sql.NullTime and provides additional methods.
type NullableTime struct {
	Time  time.Time
	Valid bool
}

// After reports whether the time instant t is after u.
func (nt NullableTime) After(u time.Time) bool {
	if !nt.Valid {
		return false
	}
	return nt.Time.After(u)
}

// MarshalJSON implements the json.Marshaler interface.
func (nt NullableTime) MarshalJSON() ([]byte, error) {
	if !nt.Valid {
		return []byte("null"), nil
	}
	return json.Marshal(nt.Time)
}

// UnmarshalJSON implements the json.Unmarshaler interface.
func (nt *NullableTime) UnmarshalJSON(data []byte) error {
	var t *time.Time
	if err := json.Unmarshal(data, &t); err != nil {
		return err
	}
	if t != nil {
		nt.Time = *t
		nt.Valid = true
	} else {
		nt.Valid = false
	}
	return nil
}

// Before reports whether the time instant t is before u.
func (nt NullableTime) Before(u time.Time) bool {
	if !nt.Valid {
		return false
	}
	return nt.Time.Before(u)
}

// Value implements the driver Valuer interface.
func (nt NullableTime) Value() (driver.Value, error) {
	if !nt.Valid {
		return nil, nil
	}
	return nt.Time, nil
}

// Scan implements the Scanner interface.
func (nt *NullableTime) Scan(value interface{}) error {
	if value == nil {
		nt.Time, nt.Valid = time.Time{}, false
		return nil
	}
	nt.Valid = true

	switch v := value.(type) {
	case time.Time:
		nt.Time = v
	case []byte:
		var err error
		nt.Time, err = time.Parse(time.RFC3339, string(v))
		if err != nil {
			return err
		}
	case string:
		var err error
		nt.Time, err = time.Parse(time.RFC3339, v)
		if err != nil {
			return err
		}
	default:
		return fmt.Errorf("cannot scan type %T into NullableTime", value)
	}
	return nil
}

// NewNullableTime creates a new NullableTime from a time.Time value.
func NewNullableTime(t time.Time) NullableTime {
	return NullableTime{Time: t, Valid: true}
}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PostgreSQL Timestamp with timezone #7149

{{title}}

Replies: 0 comments

Select a reply

PostgreSQL Timestamp with timezone #7149

ggoosen Aug 9, 2024

Replies: 0 comments

ggoosen
Aug 9, 2024