Date in GORM

Install Libraries

Make sure Git is installed on your machine and in your system’s PATH. Install the package to your $GOPATH with the go tool from shell:

$ go get github.com/go-sql-driver/mysql
$ go get -u github.com/jinzhu/gorm

Create Database

Create a database with the name is learngorm. This database have 1 tables: Product table.

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `price` double NOT NULL,
  `quantity` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `created` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`name`, `price`, `quantity`, `status`, `created`) VALUES
('Tivi 1', 4, 2, 1, '2019-07-10'),
('Tivi 2', 7, 22, 0, '2019-11-08'),
('Mobile 1', 11, 7, 1, '2019-07-11'),
('Mobile 2', 26, 8, 1, '2018-05-04'),
('Computer 1', 8, 2, 0, '2018-07-26');

Structure of Product Table

Data of Product Table

Create Entity

Create new folder named src. In src folder, create new folder named entities. In this folder, create new file named product.entity.go as below:

package entities

import (
	"fmt"
	"time"
)

type Product struct {
	Id       int `gorm:"primary_key, AUTO_INCREMENT"`
	Name     string
	Price    float64
	Quantity int
	Status   bool
	Created  time.Time
}

func (product *Product) TableName() string {
	return "product"
}

func (product Product) ToString() string {
	return fmt.Sprintf("id: %d\nname: %s\nprice: %0.1f\nquantity: %d\nstatus: %t\ncreated: %s", product.Id, product.Name, product.Price, product.Quantity, product.Status, product.Created.Format("02/01/2006"))
}

Create Config

In src folder, create new folder named config. In this folder, create new file named config.go, this file is used to connect mysql database:

package config

import (
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

func GetDB() (*gorm.DB, error) {
	dbDriver := "mysql"
	dbName := "learngorm"
	dbUser := "root"
	dbPassword := "123456"
	db, err := gorm.Open(dbDriver, dbUser+":"+dbPassword+"@/"+dbName+"?charset=utf8&parseTime=True")
	if err != nil {
		return nil, err
	}
	return db, nil
}

Create Model

In src folder, create new folder named models. In models folder, create new file named product.model.go. This file contains methods to interact with the database.

package models

import (
	"config"
	"entities"
	"time"
)

type ProductModel struct {
}

func (productModel ProductModel) FindByYearAndMonthAndDay(year int, month int, day int) ([]entities.Product, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var products []entities.Product
		db.Where("year(created) = ? and month(created) = ? and day(created) = ?", year, month, day).Find(&products)
		return products, nil
	}
}

func (productModel ProductModel) FindByDate(date time.Time) ([]entities.Product, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var products []entities.Product
		db.Where("created = ?", date).Find(&products)
		return products, nil
	}
}

func (productModel ProductModel) FindByDates(startDate time.Time, endDate time.Time) ([]entities.Product, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var products []entities.Product
		db.Where("created >= ? and created <= ?", startDate, endDate).Find(&products)
		return products, nil
	}
}

Structure of Project

Run Application

In src folder, create new file named main.go as below and use go run main.go command to run program:

package main

import (
	"fmt"
	"models"
	"time"
)

func main() {
	var productModel models.ProductModel

	fmt.Println("Find By Year and Month and Day")
	result1, _ := productModel.FindByYearAndMonthAndDay(2019, 11, 8)
	for _, product := range result1 {
		fmt.Println(product.ToString())
		fmt.Println("--------------------")
	}

	fmt.Println("Find By Date")
	date, _ := time.Parse("2006-01-02", "2019-07-10")
	result2, _ := productModel.FindByDate(date)
	for _, product := range result2 {
		fmt.Println(product.ToString())
		fmt.Println("--------------------")
	}

	fmt.Println("Find By Dates")
	startDate, _ := time.Parse("2006-01-02", "2019-07-09")
	endDate, _ := time.Parse("2006-01-02", "2019-07-15")
	result3, _ := productModel.FindByDates(startDate, endDate)
	for _, product := range result3 {
		fmt.Println(product.ToString())
		fmt.Println("--------------------")
	}
}

Output

Find By Year and Month and Day
id: 2
name: Tivi 2
price: 7.0
quantity: 22
status: false
created: 08/11/2019
--------------------

Find By Date
id: 1
name: Tivi 1
price: 4.0
quantity: 2
status: true
created: 10/07/2019
--------------------

Find By Dates
id: 1
name: Tivi 1
price: 4.0
quantity: 2
status: true
created: 10/07/2019
--------------------
id: 3
name: Mobile 1
price: 11.0
quantity: 7
status: true
created: 11/07/2019
--------------------