working_with_different_timezone_in_golang_postgreysql.go

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
	"log"
	"time"
)

const (
	HOST     = "localhost"
	PORT     = 5432
	USER     = "postgres"
	PASSWORD = "12345"
	DATABASE = "yield_fleet_planning"
)

func main() {

	// create postgre sql connection url
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable TimeZone=UTC",
		HOST, PORT, USER, PASSWORD, DATABASE)

	//connect to database
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}

	defer db.Close()

	// Insert records of different timezone
	// Take example a flight is from Bangalore India to New York
	// Depart time  2020-03-02T15:04:05+05:00 (India Standard Time)
	// Arrival time 2020-03-03T18:04:05-04:00 (Eastern Time Zone)

	//parse arrival time
	arrivalTime, err := time.Parse(time.RFC3339, "2020-03-02T15:04:05+05:00")
	if err != nil {
		log.Fatal("Error parsing arrival time", err)
	}

	//parse departure time
	departureTime, err := time.Parse(time.RFC3339, "2020-03-03T18:04:05-04:00")
	if err!=nil{
		log.Fatal("Error parsing departure time", err)
	}


	//print utc time
	fmt.Println("Arrival time UTC:",arrivalTime.UTC(), " Departure time UTC:", departureTime.UTC())

	//store records
	result, err := db.Exec("INSERT INTO flight_details VALUES (2, 'Active', 'IN'," +
		" 'USA', $1, $2)", arrivalTime.UTC(), departureTime.UTC())
	if err != nil {
		log.Fatal(err)
	}

	rowsAffected, err := result.RowsAffected()
	fmt.Println("Number of rows affected:", rowsAffected)

	//select all rows
	rows, err := db.Query("SELECT status,flight_number,departure_time,arrival_time,arrival_country," +
		"departure_country FROM flight_details;")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()


	//display rows it will display all the flights arrival and departure time in UTC format
	displayRecords(rows)


	//fetch result from different time zone
    //let say we want to get all the flight departing from India on 2020-03-02
	loc, err := time.LoadLocation("Asia/Kolkata")
	if err != nil {
		log.Fatal("unable to get time zone location")
	}

	//start time
	startDepartureTime := time.Date(2020,3,2,0,0,0,0, loc).UTC()
	endDepartureTime := time.Date(2020,3,2,23,59,59,0, loc).UTC()

	//select all rows
	rows, err = db.Query("SELECT status,flight_number,departure_time,arrival_time, arrival_country," +
		" departure_country FROM flight_details WHERE departure_time BETWEEN $1 AND $2" +
		"AND departure_country=$3;",startDepartureTime,endDepartureTime,"IN")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	log.Println("Selected rows for IN departure:")
	displayRecords(rows)

	//let say we want to get all the flight arriving at USA on 2020-03-03
	//start time
	loc, err = time.LoadLocation("America/New_York")
	if err != nil {
		log.Fatal("unable to get time zone location")
	}
	startTime := time.Date(2020,3,3,0,0,0,0, loc).UTC()
	endTime := time.Date(2020,3,3,23,59,59,0, loc).UTC()

	//select all rows
	rows, err = db.Query("SELECT status,flight_number,departure_time,arrival_time, arrival_country, " +
		"departure_country FROM flight_details WHERE arrival_time BETWEEN $1 AND $2" +
		" AND arrival_country=$3;",startTime,endTime,"USA")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	log.Println("Selected rows for USA arrival:")
	displayRecords(rows)

}

func displayRecords(rows *sql.Rows) {
	//display rows it will display all the flights arrival and departure time in UTC format
	for rows.Next() {
		var title string
		var flightNumber int
		var dt time.Time
		var at time.Time
		var ac string
		var dc string
		if err := rows.Scan(&title, &flightNumber, &dt, &at, &ac, &dc); err != nil {
			log.Fatal(err)
		}
		fmt.Println(title, flightNumber, dt, at,ac,dc)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
}

mind explaining how you got the date time in this format?

	startDepartureTime := time.Date(2020,3,2,0,0,0,0, loc).UTC()
	endDepartureTime := time.Date(2020,3,2,23,59,59,0, loc).UTC()

for example how did you get this value 2020,3,2,0,0,0,0 for startDepartureTime? is that how results get shown from DB query?

https://gist.github.com/gyaan/93a00e88293d7af77f194eeb03dda7e6#file-working_with_different_timezone_in_golang_postgreysql-go-L86-L87