Go Language
Go Language

MySQL Records Between Two Dates in Go Language


This post will allow you to search through your MySQL database and find Users that are created in between 2 dates. Using github.com/jinzhu/now package, you can easily get time-stamps in a readable format. You can view the full example on Go Playground, it will not run on go playground though since there is no MySQL database present.

db variable for a global connection throughout all your function in package. Includes the User struct, MySQL backup on bottom of this page.

1
2
3
4
5
6
7
8
var db *sql.DB
 
type User struct {
	Id            int       `json:"id"`
	Name          string    `json:"name"`
	Email         string    `json:"email"`
	CreatedAt         string    `json:"created_at"`
}

Connect to your MySQL database and run the function to fetch all users between 2 dates.

1
2
3
4
5
6
7
8
func main() {
 
	db, _ = sql.Open("mysql", "root:[email protected](localhost:3306)/mydatabase")
 
	userMap := FetchAllUsersBetween(now.BeginningOfMonth(), now.EndOfMonth())
 
	fmt.Println("Amount of Users: " ,len(userMap))
}

Using github.com/jinzhu/now package, you can use now.BeginningOfMonth(), now.EndOfMonth(), now.EndOfYear(), etc.

Function to fetch all Users in between 2 dates. This function will return an array of Users that are in the specific date ranges of 2 dates.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
func FetchAllUsersBetween(from time.Time, to time.Time) []User {
	formatedFrom := from.Format("2006-01-02 15:04:05")
	formatedTo := to.Format("2006-01-02 15:04:05")
	timeColoumn := "created_at"
	rows, _ := db.Query("SELECT * FROM users WHERE "+timeColoumn+" BETWEEN '"+formatedFrom+"' AND '"+formatedTo+"'")
	var id int
	var name, email, created_at string
	var users []User
 
	for rows.Next() {
		err := rows.Scan(&id, &name, &email, &created_at)
		if err != nil { /* error handling */}
		users = append(users, User{Id: id, Email: email, Name: name, CreatedAt: created_at})
	}
 
	return users
}

FetchAllUsersBetween needs 2 parameters, ‘from’ Time, and ‘to’ Time. Which is created by now package. Be sure to change line #4 to fit the column for your MySQL database. You can also import the SQL backup below.

SQL backup of Users table. Includes created_at DATETIME.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL,
  `email` text NOT NULL,
  `name` text NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
 
INSERT INTO `users` (`id`, `email`, `name`, `created_at`) VALUES
(5, [email protected]', 'Example User', '2016-10-04 00:00:00'),
(6, [email protected]', 'Another User', '2016-10-06 00:00:00'),
(7, [email protected]', 'Another Userzz', '2016-10-03 00:00:00'),
(8, [email protected]', 'Another User3', '2016-10-04 06:00:00'),
(9, [email protected]', 'Another User5', '2016-10-05 10:00:00'),
(10, [email protected]', 'Another User7', '2016-10-21 10:00:00');
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `users`
  MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

https://play.golang.org/p/CZQBuRXVr7


View Comments
There are currently no comments.