Convert Time from MySQL, Angular(cli), and Go(srv)

Convert time zone in MySQL, Angular, and Go
MySQL:

#SELECT userTimezone FROM USER_PREFERENCE WHERE uid = 1;

SELECT
UploadFileID
, UserID
, Filename AS OrigFilename
, CONCAT(Filename, ' <=== this is my filename') AS Filename
, FilenameHashed
#, CreateTime AS OrigCreateTime
, CONCAT(CreateTime + INTERVAL +8 HOUR, ' UTC') AS CreateTime
FROM UploadFile
ORDER BY UploadFileID DESC

Angular(Client side): Do it in display side. Convert rawdata is usually not a good practice.

export class MyruleUploadGridComponent implements OnInit {
    // Global variables
    private gridApi;
    private gridColumnApi;

    public columnDefs = [
        {
            field: 'UploadFileID',
            headerName: 'UploadFileID',
            sortable: true,
            sortingOrder: ['asc', 'desc'],
            headerTooltip: 'asdf',
        },
        {
            field: 'UserID',
            headerName: 'UserID',
        },
        {
            field: 'Filename',
            headerName: 'Filename',
        },
        {
            field: 'CreateTime',
            headerName: 'CreateTime',
            cellRenderer: (params) => {
                return new DatePipe('en-Us').transform(params.value + ' UTC', 'short', 'locale')
            },
        },
    ];
}

Go(backend):
Method1:

var t time.Time
if t, err = TimeIn(colDef.CreateTime, "Canada/Pacific"); err != nil {
 fmt.Printf("err: %v\n", err)
}

colDef.CreateTime = t.String()

func TimeIn(inputTime string, name string) (time.Time, error) {
 var t time.Time
 var err error
 layout := "2006-01-02 15:04:05"
 if t, err = time.Parse(layout, inputTime); err != nil {
  fmt.Printf("err:%v\n", err)
 }
 loc, err := time.LoadLocation(name)
 if err == nil {
  t = t.In(loc)
 }
 return t, err
}

Method2:

package main

import (
 "fmt"
 "time"
)

func main() {

 d1 := "2020-07-29 21:47:11"
 newT1 := AddHour(d1, 51)
 fmt.Printf("Adding 1 hour\n: %s\n", newT1)
}

func AddHour(d string, numOfHour int) time.Time {
 t, _ := ConvStrToTime(d)

 newT := t.Add(time.Hour * time.Duration(numOfHour))
 return newT
}

func ConvStrToTime(str string) (time.Time, error) {
 layout := "2006-01-02 15:04:05" // Mon Jan 2 15:04:05 -0700 MST 2006
 return time.Parse(layout, str)
}

PHP MySQL Login System

PHP MySQL Login System

Building the register system:
Step1: creating the database table
CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );

INT: Stores numeric values in the range of -2147483648 to 2147483647
NOT NULL: constraint the field cannot accept a NULL value.
PRIMARY KEY: constraint; marks the corresponding field as the table's primary key.
AUTO_INCREMENT: a MySQL extension to standard SQL, which tells MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1. Only available for numeric field.
VARCHAR():Stores variable-length strings with a maximum size of 65,535 characters.
N:CHAR():Stores fixed-length strings with a maximum size of 255 characters.
UNIQUE: ensures that each row for a column must have a unique value.
DATETIME: Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format.
CURRENT_TIMESTAMP: Stores timestamp values. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:01' UTC).


Create the Config File to connect the MySQL database server.

<?php /* Database credentials. Assuming you are running MySQL server with default setting (user 'root' with no password) */ define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'root'); define('DB_PASSWORD', '123456'); define('DB_NAME', 'demodb'); /* Attempt to connect to MySQL database */ $link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } ?>

Create the Registration Form.
<?php // Include config file require_once "config.php"; // Define variables and initialize with empty values $username = $password = $confirm_password = ""; $username_err = $password_err = $confirm_password_err = ""; // Processing form data when form is submitted if($_SERVER["REQUEST_METHOD"] == "POST"){ // Validate username if(empty(trim($_POST["username"]))){ $username_err = "Please enter a username."; } else{ // Prepare a select statement $sql = "SELECT id FROM users WHERE username = ?"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "s", $param_username); // Set parameters $param_username = trim($_POST["username"]); // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ /* store result */ mysqli_stmt_store_result($stmt); if(mysqli_stmt_num_rows($stmt) == 1){ $username_err = "This username is already taken."; } else{ $username = trim($_POST["username"]); } } else{ echo "Oops! Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); } // Validate password if(empty(trim($_POST["password"]))){ $password_err = "Please enter a password."; } elseif(strlen(trim($_POST["password"])) < 6){ $password_err = "Password must have atleast 6 characters."; } else{ $password = trim($_POST["password"]); } // Validate confirm password if(empty(trim($_POST["confirm_password"]))){ $confirm_password_err = "Please confirm password."; } else{ $confirm_password = trim($_POST["confirm_password"]); if(empty($password_err) && ($password != $confirm_password)){ $confirm_password_err = "Password did not match."; } } // Check input errors before inserting in database if(empty($username_err) && empty($password_err) && empty($confirm_password_err)){ // Prepare an insert statement $sql = "INSERT INTO users (username, password) VALUES (?, ?)"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "ss", $param_username, $param_password); // Set parameters $param_username = $username; $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ // Redirect to login page header("location: login.php"); } else{ echo "Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); } // Close connection mysqli_close($link); } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Sign Up</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css"> <style type="text/css"> body{ font: 14px sans-serif; } .wrapper{ width: 350px; padding: 20px; } </style> </head> <body> <div class="wrapper"> <h2>Sign Up</h2> <p>Please fill this form to create an account.</p> <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post"> <div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>"> <label>Username</label> <input type="text" name="username" class="form-control" value="<?php echo $username; ?>"> <span class="help-block"><?php echo $username_err; ?></span> </div> <div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>"> <label>Password</label> <input type="password" name="password" class="form-control" value="<?php echo $password; ?>"> <span class="help-block"><?php echo $password_err; ?></span> </div> <div class="form-group <?php echo (!empty($confirm_password_err)) ? 'has-error' : ''; ?>"> <label>Confirm Password</label> <input type="password" name="confirm_password" class="form-control" value="<?php echo $confirm_password; ?>"> <span class="help-block"><?php echo $confirm_password_err; ?></span> </div> <div class="form-group"> <input type="submit" class="btn btn-primary" value="Submit"> <input type="reset" class="btn btn-default" value="Reset"> </div> <p>Already have an account? <a href="login.php">Login here</a>.</p> </form> </div> </body> </html>