Beginners Guide to SQL – SQL Dates and Times

Hits: 8

(SQL Tutorials for Citizen Data Scientist)

SQL Dates and Times

In this tutorial you will learn how to work with dates and times in SQL.

Date and Time Manipulation

Along with strings and numbers, you often need to store date and/or time values in a database, such as an user’s birth date, employee’s hiring date, date of the future events, the date and time a particular row is created or modified in a table, and so on.

This type of data is referred as temporal data and every database engine has a default storage format and data types for storing them. The following table shows the data types supported by the MySQL database server for handling the dates and times.

Type Default format Allowable values
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31
TIME HH:MM:SS or HHH:MM:SS -838:59:59 to 838:59:59
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 to 2037-12-31 23:59:59
YEAR YYYY 1901 to 2155

DATE values have YYYY-MM-DD format, where YYYY represent the full year (4 digits), whereas the MM and DD represents the month and day parts of the date respectively (2 digits with leading zero). Likewise, the TIME values have normally HH:MM:SS format, where HHMM, and SS represents the hours, minutes, and seconds parts of the time respectively.

The following statement demonstrates how to insert a date value in database table:

Example

INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);

Note: In MySQL the hours part of the TIME values may be larger, because MySQL treats them as elapsed time. That means the TIME data type can be used not only to represent a time of day (which must be less than 24 hours), but also a time interval between two events which may be greater than 24 hours, or even negative.


Tracking Row Creating or Modification Times

While working with the database of a large application you often need to store record creation time or last modification time in your database, for example, storing the date and time when a user sign up, or when a user last updated his password, etc.

In MySQL you can use the NOW() function to insert the current timestamp, as follow:

Example

-- Syntax for MySQL Database 
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());

However, if you don’t want to insert current date and time manually, you can simply use the auto-initialization and auto-update properties of the TIMESTAMP and DATETIME data types.

To assign automatic properties, specify the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions, as follow:

Example

-- Syntax for MySQL Database 
CREATE TABLE users (
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    birth_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Note: Automatic initialization and updating for DATETIME data type is only available in MySQL 5.6.5 or later. If you’re using an older version use the TIMESTAMP instead.


Extracting Parts of Dates or Times

There may be situations where you just want to obtain a part of date or time. In MySQL you can use the functions specifically designed for extracting part of a temporal value, such as YEAR()MONTH()DAYOFMONTH()MONTHNAME()DAYNAME()HOUR()MINUTE()SECOND(), etc.

The following SQL statement will extract the year part of the birth_date column values, e.g. if the birth_date of any user is 1987-01-14 the YEAR(birth_date) will return 1987.

mysql> SELECT name, YEAR(birth_date) FROM users;

Similarly, you can use the function DAYOFMONTH() to get the day of the month, e.g. if the birth_date of any user is 1986-10-06 the DAYOFMONTH(birth_date) will return 6.

mysql> SELECT name, DAYOFMONTH(birth_date) FROM users;

Formatting Dates or Times

If you want more descriptive and readable date format in your result set, you can use the DATE_FORMAT() and TIME_FORMAT() functions to reformat the existing date and time values.

The following SQL statement will format the values of birth_date column of the users table in more readable format, like the value 1987-01-14 to January 14, 1987.

mysql> SELECT name, DATE_FORMAT(birth_date, ‘%M %e, %Y’) FROM users;

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!