MySQL Records Between Two Dates in Go Language
Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /usr/www/phpsites/public/yayprogramming/wp-content/plugins/wp-syntax/wp-syntax.php on line 383
Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /usr/www/phpsites/public/yayprogramming/wp-content/plugins/wp-syntax/wp-syntax.php on line 383
Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /usr/www/phpsites/public/yayprogramming/wp-content/plugins/wp-syntax/wp-syntax.php on line 383
Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /usr/www/phpsites/public/yayprogramming/wp-content/plugins/wp-syntax/wp-syntax.php on line 383
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:password@tcp(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, 'info@domain.com', 'Example User', '2016-10-04 00:00:00'), (6, 'info@anotherdomain.com', 'Another User', '2016-10-06 00:00:00'), (7, 'info@anotherdomainzz.com', 'Another Userzz', '2016-10-03 00:00:00'), (8, 'info@anotherdomainz2.com', 'Another User3', '2016-10-04 06:00:00'), (9, 'info@anotherdomaina.com', 'Another User5', '2016-10-05 10:00:00'), (10, 'info@anotherdomainp.com', '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; |