MySQL Date Format And Timestamp Functions With Examples

This comprehensive tutorial provides syntax and examples of MySQL Date Format and Timestamp functions like current date, add date, add time etc.:

In this tutorial, we will learn in-depth about the MySQL Date and Time data types and the various functions provided by MySQL for manipulating date and time values.

MySQL provides a great set of functions/utilities to perform different operations like adding and subtracting dates, finding out the difference between 2 dates, and extracting specific parts of given date-time value.

=> Click here for the complete MySQL tutorial series

MySQL Date Format And Time Functions

These functions are very important whenever any operations on date time column values are required in MySQL tables.

Pre-Requisites

For running the examples used in the tutorial, you would need a local setup of MySQL installation.

=>> For installing MySQL refer to our tutorial here.

DATE And TIME Data Types

Data types include YEAR, DATE, TIME, DATETIME, and TIMESTAMP.

MySQL stores date and time values using 5 different data types i.e. – YEAR, DATE, TIME, DATETIME, and TIMESTAMP. Let’s try to understand these briefly in the below table, before diving deeper into DATE FORMATS and DATETIME FUNCTIONS provided by MySQL.

Now we will understand the memory requirements for fractional-storage. For TIME, DATETIME, and TIMESTAMP data types depending on the fractional component specified, the memory consumed would be different.

Using the above table, for example, if we have a column type as – TIME(5).

Then total memory used would be => memory used by TIME data type + memory used by 5 fractional component => 3 + 3 => 6 bytes

Let’s illustrate all these data types as part of a single table and see the values that are assigned to each of the types.

CREATE TABLE datetime_illustration(year_col YEAR, date_col DATE, time_col TIME, 
time_col_fractional TIME(2), datetime_col DATETIME, datetime_col_fractional DATETIME(6), 
timestamp_col TIMESTAMP, timestamp_col_fractional TIMESTAMP(6)) 

Insert a single row in the table using the ‘now()’ function, which is the current timestamp. Notice how using this function would result in trimming/inserting only the required part of the current date and time, depending on the data type of the column.

insert into datetime_illustration values (now(), now(), now(), now(), now(), now(), now(), now());
select * from datetime_illustration

Now, let’s see the contents of the table to analyze the data in different columns.

select * from datetime_illustration

So, we summarise the above data as,

• All columns got assigned values per the data types, for example, for the first column – YEAR – though we inserted using ‘now()’ function which is the current timestamp, the value that got inserted is just the year part of the current date time – i.e. 2020.
• now() function returns the current datetime in the format – dd:mm:yyyy hh:mm:ss
• Notice the fractional part in the column names having ‘_fractional’. Depending upon the number of fractional digits specified, the fractional values are captured and displayed.

Automatic Initialization and Updation of DATETIME and TIMESTAMP Fields

In the real-life scenarios – most of the time, the DATETIME initialization and updates are configured to be automatic.

What this means is generally tables have 2 columns like – created_time and updated_time.

created_time column is configured to take the value of the current system time whenever the row is inserted, and similarly updated_time is configured to take the current system time whenever there are any updates done to a particular row of the table.

Please note that, during the time of row insertion, both created_time and updated_time would have the same value. All subsequent updates to the row would result in updating the field updated_time with the current system time.

Let’s try creating one such table which has automatic initialization as well as updation.

We will create a table named stock_price which has the stock symbol, price, and datetime fields – created_on and updated_on.

create table stock_price(symbol varchar(100), price decimal(10,2),
 created_on datetime default current_timestamp, updated_on datetime
 default current_timestamp on update current_timestamp) 

Insert some data into the table:

insert into stock_price(symbol,price) values('AMZN','3000.10');

Let’s see the result (notice we have just entered values for symbol and price – the datetime columns, created_on, and updated_on should be automatically initialized)

select * from stock_price

Try updating the stock price for the ‘AMZN’ stock as shown below:

update stock_price set price='3005.15' where symbol='AMZN'

And now, fetch the data again for the table. We should see the value of the updated_time getting changed to whatever time the update to the stock_price happened.

select * from stock_price

You can now notice from the above example that the value of the updated_on column was updated to the update timestamp.

MySQL Date And Time Functions

MySQL provides numerous functions for columns having date and time related data types.

These functions are generally used to,

• Convert a stored date to a required format while querying the data.
• Manipulating existing date time fields, like adding an interval to the current value of the column, subtracting hours from the current set values, etc.
• Extracting specific info from the stored values, for example, extracting just the “date” part from the stored value and doing further filtering/operations like GROUP BY with the date, etc.

In this section, we will see examples against the most commonly used Date and Time functions.

>> Refer here for a complete list of such functions.

#1) Getting Current Date And Time

MySQL provides a lot of handy functions/constant values to fetch the current values of date and time and are generally used while storing values in the datetime columns or retrieving against any particular range from the current date and time values.

a) curdate()

This function gets the current date. Other synonyms of curdate() function are – CURRENT_DATE, CURRENT_DATE(), CURRENT_DATE(fractionalDigits)

Example of this function is shown below:

select curdate(), CURRENT_DATE(), CURRENT_DATE

b) curtime()

This function gets the current time. To obtain a fractional part (up to 6 digits) specify the fraction in the parentheses as curtime(3) This would fetch the current value of system time up to 3 decimal places.

Other synonyms of curtime() function are- CURRENT_TIME, CURRENT_TIME(), CURRENT_TIME(fractionalDigits)

select curtime(), CURRENT_TIME(), CURRENT_TIME, curtime(6), CURRENT_TIME(6)

c) now()

This function gets the current timestamp. Specify fractional part in the parentheses, for example, now(4) would fetch the current timestamp up to 4 places of decimal.

Other synonyms of now() function include CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(fractionalDigits)

select now(), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, now(6), CURRENT_TIMESTAMP(6)

#2) Adding And Subtracting Dates

MySQL provides functions to add and subtract date and time values to an existing value stored in the column or to be used during querying data. We will understand the functions with the help of examples.

We will be using the below table for performing date-time addition to subtraction.

create table datetime_calc(datecol DATE, timecol TIME, datetimecol DATETIME(6));

Insert some entries in the above table using the INSERT statements as shown below:

insert into datetime_calc values(now(), now(), now(6));
insert into datetime_calc values(now(), now(), now(6));

Let’s query the above table and see the data stored:

In addition to updating columns in a table, we can directly do a datetime manipulation using SELECT statement by applying on any specific date/time or datetime value.

a) ADDDATE()

As the name suggests, this function is used to add a date to an existing date value which could be of type – DATE, DATETIME, or TIMESTAMP

Syntax:

ADDDATE(inputTimeValue/fieldName, noOfDays)

ADDDATE can also be called with Interval value – which could be DAYS, WEEK, HOUR, MINUTE, SECOND, etc.

Syntax, in that case, would change to,

ADDDATE(inputTimeValue/fieldName, Interval intervalUnit)

Let’s see a couple of examples to understand this better.

SELECT ADDDATE('2020-08-15', 31);

// output
'2020-09-15'

When an integer value is supplied as the second argument, it is treated as the number of days to be added to the given date.

Some more examples with Interval as – MONTH, HOUR, and WEEK are:

SELECT ADDDATE('2020-01-02', INTERVAL 1 MONTH);

//output
2020-02-02

SELECT ADDDATE('2020-01-02 12:11:11', INTERVAL 5 HOUR);

//output
2020-01-02 17:11:11

SELECT ADDDATE('2020-01-02 12:11:11', INTERVAL 1 WEEK);

//output
2020-01-09 12:11:11

Other examples of intervals could be – MINUTE, SECOND, QUARTER, etc.

For updating a column of the existing table, for example, for the table we created ‘datetime_calc’ if we want to update the ‘datecol’ value and add 10 days to all the dates in the column, we could use the query as below:

update datetime_calc set datecol=ADDDATE(datecol, 30);

Now, use SELECT * to show all the data in the table datetime_calc.

SELECT * FROM datetime_calc

We can see in the above output that the values of the datecol column have been updated to 24-09-2020 from 25-08-2020 after adding 30 days to the existing values.

b) ADDTIME()

Similar to ADD_DATE, the ADD_TIME function can be used to add a time component to an existing – DATETIME, TIME, or TIMESTAMP column/field or given input.

Syntax:

ADDTIME(inputTimeValue/fieldName, ‘hh:mm:ss’)

Example:

SELECT ADDTIME('2020-01-02 12:11:11', '01:10:00');

// output
2020-01-02 13:21:11

Let’s look at an example where we are updating a datetime column in a table by adding 5 hours to all the values within that column. Here’s a sample query :

update datetime_calc set datetimecol=ADDTIME(datetimecol, '05:00:00')

Table output before:

// output
'2020-08-25 05:53:37.851379'
'2020-08-25 05:53:39.711515'

Table output after:

// output
'2020-08-25 10:53:37.851379'
'2020-08-25 10:53:39.711515'

c) SUBDATE()

SUBDATE is exactly similar to ADDDATE, the only difference being, SUBDATE subtracts the given Interval/No. of days from the column/field value.
Syntax:

SUBDATE(inputTimeValue/fieldName, noOfDays)

Let’s look at a couple of examples to illustrate the SUBDATE() function.

SELECT SUBDATE('2020-08-15', 31);

// output
2020-07-15

The below example shows subtracting an Interval mentioned in hours/days week, etc.

SELECT SUBDATE('2020-01-02 12:11:11', INTERVAL 1 WEEK);

// output
2019-12-26 12:11:11

SELECT SUBDATE('2020-01-02 12:11:11', INTERVAL 12 HOUR);

// output 
2020-01-02 00:11:11

d) SUBTIME()

SUBTIME subtracts the time value specified in ‘hh:mm:ss’ from a given datetime or time field value.

Syntax:

SUBTIME(inputTimeValue/fieldName, ‘hh:mm:ss’)

Examples of SUBTIME() function:

SELECT SUBTIME('2020-01-02 12:11:11', '02:20:05');

// output
2020-01-02 09:51:06

#3) Converting Dates

MySQL provides a variety of in-built functions from converting dates from one format to another. Let’s see the most widely used functions for converting dates.

a) CONVERT_TZ()

This function is used to convert the date from one timezone to another. These come handy in situations where suppose your data was stored using UTC timezone and while displaying you want to convert into timezone of your choice.

For example, convert UTC to MET (Middle European Time).

SELECT CONVERT_TZ('2004-01-01 12:00:00', 'UTC', 'MET');

Tips/Notes

#1) To get the list of all timezones in MySQL system database, you can run the below query and use any of the timezone names for converting from one timezone to another.

select * from mysql.time_zone_name

#2) The timezone info is populated during the installation of the MySQL server instance. If the result of the above query is NULL, then you may first need to populate timezone values in the MySQL system tables.

>> Follow this link for more help on this.

b) FROM_UNIXTIME()

This function is used to convert a given UNIX TIMESTAMP to MySQL datetime format.

SELECT FROM_UNIXTIME(1547430881);

//output
2019-01-14 07:24:41

c) UNIX_TIMESTAMP

This function is used to convert a given MySQL datetime to UNIX timestamp.

SELECT UNIX_TIMESTAMP('2020-03-15 07:10:56.123')

//output
1584236456.123

UNIX timestamp is the representation of a given date in the form of seconds elapsed since January 1, 1970, UTC.

>> Refer here for more details about UNIX timestamp. 

#4) Fetching Specific Parts Of DateTime Columns

At times, it is desired to fetch a specific part of the date value for a datetime field value in the table. For example, suppose you want to count the no. of orders and group them by day, week, month, etc.

And, generally speaking, these values are not stored as separate columns in the table – for example, you would have just one column like – created_on which would be a datetime field and not having separate columns like – date, month, year, etc.

MySQL provides a lot of useful methods where you can extract the desired part from the DateTime field.

Suppose we have a datetime value as – ‘2020-03-15 07:10:56.123’

Let’s have a look at some of the widely used functions with examples using the above datetime value.

a) Extract DAY

MySQL provides the following functions to extract DAY information:

DATE() – Extract the DATE() part of DateTime field.

SELECT DATE('2020-03-15 07:10:56.123');

//output
2020-03-15

DAYOFMONTH() – Extract the DAY part of datetime. Its essentially the day of the month. Shorthand for using this function is DAY()

SELECT DAYOFMONTH('2020-03-15 07:10:56.123');

//output
15

DAYOFWEEK() – Extracts the index corresponding to the day of week – values between 1 to 7.

SELECT DAYOFWEEK('2020-03-15 07:10:56.123');

//output
1

DAYOFYEAR() – Extracts the day in terms of no. of days passed in a year. The values range between 1 and 365 (or 366 if it is a leap year).

SELECT DAYOFYEAR('2020-03-15 07:10:56.123');

//output
75

DAYNAME() – Extracts the name of the day with values ranging from Monday to Sunday.

SELECT DAYNAME('2020-03-15 07:10:56.123');

//output
Sunday

b) Extract TIME

MySQL provides the following functions to extract TIME values :

TIME() – Extracts the time portion in format hh:mm:ss (with fractional seconds component if available).

SELECT TIME('2020-03-15 07:10:56.123');

//output
'07:10:56.123'

HOUR() – Extracts the hour part of the field/value – values ranging between 1-24.

SELECT HOUR('2020-03-15 07:10:56.123');

//output
7

MINUTE() – Extracts the minute part of the given datetime – values ranging between 1-60.

SELECT MINUTE('2020-03-15 07:10:56.123');

//output
10

SECOND() – Extracts the seconds part of the given datetime – values ranging between 1-60.

SELECT SECOND('2020-03-15 07:10:56.123');

//output
56

MICROSECOND() – Extracts the microseconds part of the given DateTime.

SELECT MICROSECOND('2020-03-15 07:10:56.123');

//output
123000

c) Extract MONTH

MONTH() – Extracts the month index. Values ranging between 1 – 12.

SELECT MONTH('2020-03-15 07:10:56.123');

//output
3

MONTHNAME() – Extracts the month name. Values ranging between January to December.

SELECT MONTHNAME('2020-03-15 07:10:56.123');

//output
March

d) Extract WEEK, YEAR, and QUARTER

WEEK() – Extracts the WEEK of year for the given DateTime value.

SELECT WEEK('2020-03-15 07:10:56.123');

//output
11

YEAR() – Extracts the YEAR part of the date.

SELECT YEAR('2020-03-15 07:10:56.123');

//output
2020

QUARTER() – Extracts the quarter with respect to the year. Values ranging between 1 to 4.

SELECT QUARTER('2020-03-15 07:10:56.123');

//output
1

All these above methods are mostly used for data analysis and making predictions – for example, which days of the month were the no. of orders highest, etc.

#5) Finding Difference Between 2 Dates

In order to find the difference between 2 given datetime field values, MySQL provides DATEDIFF() & TIMEDIFF() functions.

a) DATEDIFF()

Returns the difference between 2 DateTime (or date) values in no of days.

Syntax:

DATEDIFF(DateTime1, DateTIme2)

Let’s see some examples for DATEDIFF() function:

DATEDIFF() is using DateTime inputs.

SELECT DATEDIFF('2020-01-05 12:11:11.11', '2020-01-03 14:11:11')

//output
2

DATEDIFF() using Date inputs.

SELECT DATEDIFF('2020-04-02', '2020-01-04')

//output
89

DATEDIFF() using Date inputs with negative output.

SELECT DATEDIFF('2020-04-02', '2020-04-05')

//output
-3

b) TIMEDIFF()

This function returns the difference between 2 datetime (or time) values expressed in ‘hh:mm:ss’ with an optional fractional part depending on the input the TIMEDIFF() function was called with.

Syntax:

TIMEDIFF(DateTime1, DateTIme2)

As per the above syntax, the result of TIMEDIFF() function would be time specified as ‘hh:mm:ss’ for expression=> DateTime1 – DateTIme2

Please note that,

• Both Expression 1 & Expression2 are of same type – i.e. one datetime, another just time, would return a NULL value.
• Expression1 and Expression2 cannot be just dates. Example: DATEDIFF(2020-01-01, 2020-02-01) would return NULL.

Let’s see some examples for TIMEDIFF() function:

TIMEDIFF() using DateTime inputs.

SELECT TIMEDIFF('2020-01-4 12:11:11.11', '2020-01-03 12:11:11')

//output
24:00:00.11

TIMEDIFF() using Time inputs.

SELECT TIMEDIFF('12:11:11.11', '02:12:11')

//output
09:59:00.11

TIMEDIFF() returning negative output.

SELECT TIMEDIFF('2020-01-01 12:11:11.11', '2020-01-03 12:11:11')

//output
-47:59:59.89

Please note that both DATEDIFF() and TIMEDIFF() can return NEGATIVE values whenever the first argument is lesser than the second one – i.e. the first argument being an older dateTime value as compared to the second one.

#6) Formatting Dates

DATE_FORMAT() function converts the date in the given format.

For example: Converting the required date in format mm/yyyy.

SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%m/%Y');

//output
03/2020

Let’s see some common conventions for using format specifiers:

Depending on the requirement these format specifiers can be used and MySQL DateTime can be converted into required format.

Example: Converting to dd/mm/yyyy format can be specified as:

SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d/%m/%Y');

//output
15/03/2020

>> Refer here for a complete list of format specifiers supported by MySQL.

Frequently Asked Questions

Q #1) How can we change the year part of the stored date in MySQL?

Answer: In order to update the year value to a particular number for a given date, we can make use of the DATE_ADD function and add the difference between the current year of date and the one that we want to change.

For Example: Suppose we have the date ‘2020-03-15 07:10:56.123’ and we just want to change the year portion of the date to 2018. In order to do that,

• We can first find the difference between the year to be updated and the actual year of the date. This could be achieved using the YEAR function.

SELECT (2018 - year('2020-03-15 07:10:56.123'))

//output
-2

• Now, we can add this -2 as year interval using the DATE_ADD function.

SELECT  DATE_ADD('2020-03-15 07:10:56.123', INTERVAL -2 year)

//output
2018-03-15 07:10:56.123000

You can see above that we have just changed the year part of the date.

The same set of steps can be used when there is a requirement to change a specific part of the date. Example: Month, Day, etc.

Q #2) How to change datetime in the required format in MySQL?

Answer: MySQL provides DATE_FORMAT() function to convert datetime in required formatted string for displaying in query results.

>> Refer here for a complete list of format specifiers.

Example: For converting datetime to format – dd:mm:yyyy, you can use the following query:

SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d:%m:%Y');

Q #3) What is the date format in MySQL?

Answer: The standard date format in MySQL is ‘dd-mm-yyyy hh:mm:ss.{fractionalPart}

Sample datetime value stored in MySQL – ‘2020-03-15 07:10:56.123’

Q #4) How to create a table with DATE datatype in MySQL.

Answer: MySQL provides different data types for storing date and time values as per need. For just storing dates – DATE data type can be used. It stores data in the format of yyyy-mm-dd.
Let’s see example of specifying column as DATE type:

CREATE TABLE date_illustration(date_col DATE);

Conclusion

In this tutorial, we learned about the different data types that are provided by MySQL for storing date and time values and how various easy to use functions provided by MySQL can be utilized to perform calculations on date time values.

We also learned about different functions like extracting specific parts of the given DateTime value, converting a given date into another form, adding date/time values to existing values, etc.

DateTime is one of the most important data types in MySQL and has a lot of importance for fetching details around when an event happened.

These values are very widely used by data analysts/machine learning/pricing models etc. where a lot of slicing and dicing of dates is done in order to prepare machine learning models and take critical business decisions.