Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

SQL time

Let's see examples and practice with a database table

Suppose you have a database name database and inside that, you have a table named DataBaseTable which has a date_time column. In the date column, you have date and time.

CURRENT_TIME()

This function will show the server's current time.

SELECT date_time_col, CURRENT_TIME(date_time_col) AS Only_date from database.DataBaseTable;

CURRENT_TIMESTAMP()

This function will show the server's current time and date.

SELECT date_time_col,CURRENT_TIME(date_time_col) AS Only_date from database.DataBaseTable;

TIME()

If you pass date and time then this function will return you only the time.

SELECT date_time_col,TIME(date_time_col) AS Only_date from database.DataBaseTable;

HOUR()

If you pass date and time or only time then this function will return you only the hour.

SELECT date_time_col,HOUR(date_time_col) AS Only_date from database.DataBaseTable;

MINUTE()

If you pass date and time then this function will return you only the minute.

SELECT date_time_col,MINUTE(date_time_col) AS Only_date from database.DataBaseTable;

SECOND()

If you pass date and time then this function will return you only the second.

SELECT date_time_col,SECOND(date_time_col) AS Only_date from database.DataBaseTable;

TIMEDIFF()

If you pass two different times then this function will return the difference between those two times.

SELECT date_time_col_1,date_time_col_2,TIMEDIFF(date_time_col_1,date_time_col_2) AS Only_date from database.DataBaseTable;

ADDTIME()

If you pass time and an interval then this function will add the interval time with the given time and will return you a new time.

SELECT date_time_col_1,date_time_col_2,ADDTIME(date_time_col_1,"5:14:17") AS Only_date from database.DataBaseTable;

SUBTIME()

If you pass time and an interval then this function will subtract interval time from the given time will return a new time.

SELECT date_time_col_1,date_time_col_2,SUBTIME(date_time_col_1,"5:14:17") AS Only_date from database.DataBaseTable;

MAKETIME()

This function will create a new time.To do this pass three parameters.First is for hours, then minute and second.

SELECT date_time_hour_col,date_time_min_col,date_time_sec_col,SUBTIME(date_time_hour_col,date_time_min_col,date_time_sec_col) AS Only_date from database.DataBaseTable;

TIME_FORMAT

Time format is divided into four parts:
Hour:For hour you have four options:
  %h-->It will show hours from 1 to 12. After 12 it will show 1. It will add
    0 before one digit hour.
  %H-->It will show hours from 1-24. It will add a 0 before one digit hour.
  %g-->It will show hours from 1 to 12. After 12 it will show 1. It will not add
    0 before one digit hour.
  %G-->It will show hours from 1-24.It will not add add 0 before one digit hour.

Minutes:For minutes write %i

Seconds:For seconds write %s

Microseconds: For microseconds write %f

For AM and PM write %P

Let's see example:
SELECT TIME_FORMAT("10:05:10","%H %i %s") AS time;

SELECT TIME_FORMAT("10:05:10","%H:%i:%s %p") AS time;

You can use anything as a separator like /,-,: etc.

SELECT date_time_hour_col,date_time_min_col,date_time_sec_col,SUBTIME(date_time_hour_col,date_time_min_col,date_time_sec_col) AS Only_date from database.DataBaseTable;

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us