MariaDB DateTime Tutorial + Examples

In this MariaDB tutorial, we will study the use of the MariaDB DateTime constraint and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB DateTime
• MariaDB DateTime vs timestamp
• MariaDB DateTime Default Now
• MariaDB DateTime to Date
• MariaDB DateTime with Timezone
• MariaDB DateTime Milliseconds
• MariaDB DateTime Default Current_Timestamp
• MariaDB DateTime to String
• MariaDB DateTime Difference

MariaDB DateTime

In MariaDB, the DateTime value is a combination of Date and time. It displays DATETIME values in the ‘YYYY-MM-DD HH:MM: SS.ffff’ format and assigns the value to DATETIME columns using either string or numbers. It only accepts CURRENT_TIMESTAMP as the default value.

The syntax of the DateTime constraint is given below:

SYNTAX:

DATETIME [(microsecond precision)]

The sample example of the DateTime constraint is given below:

EXAMPLE:

CREATE TABLE usa_datetime( D DateTime);

INSERT INTO usa_datetime values
("2011-03-11 14:22:10"), 
("2012-04-19 13:08:22"),
("2013-07-18 13:44:22.123456");

SELECT * FROM usa_datetime;

Also, check: How to Create Table in MariaDB

MariaDB DateTime vs TimeStamp

In MariaDB, the difference between DateTime vs TimeStamp is given below:

DATETIME	TIMESTAMP
It has eight bytes	It has four bytes.
If we want to store a specific value we should use the DateTime field.	Every time it is generally used to track changes of records and it is often updated whenever the record is changed.
It displays DateTime format in “YYYY-MM-DD HH:MM:SS:ffff” format. Syntax: DATETIME [(microsecond precision)]	It has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. Syntax: TIMESTAMP [(microsecond precision)]

The sample example of the DateTime constraint is given below:

SELECT CONVERT("0001-01-01",DATETIME) as Earth_NewDay;

As we see in the above query, we have used the CONVERT() function to convert expr1 value as “0001-01-01” Date value with the DateTime constraint and converted it by adding an alias name as “Earth_NewDay” column.

The sample example of the TIMESTAMP constraint is given below:

SELECT time_sec FROM usa_datetime;

As we see in the above query, we have used timestamp constraint by adding a new column as time_Sec from the usa_datetime table.

[Note]: When we try to add a new column time_Sec by using timestamp constraint, we didn’t need to add new rows in it by using the INSERT statement. It automatically added time_Sec values by current date and time from the user’s computer.

Read: How to create a user in MariaDB

MariaDB DateTime Default Now

In this section, we will talk about MariaDB Now() function to bring the current date and time from the user’s computer.

MariaDB Now() returns the current date and time as a value in the “YYYY-MM-DD HH:MM: SS” or “YYYYMMDDHHMMSS.uuuuu” format. The function uses string or numeric context and returns a value in the current timezone.

The syntax of the NOW() function is given below:

NOW([precision])

The sample example of the NOW() function is given below:

SELECT NOW() as Current_time_and_Date;

As we see in the above query, we have used the NOW() function to give the current date and time and we have put a new alias column name as “Current_time_and_Date” to bring output as “2022-01-18 12:36:34” in it.

Read: How To Check MariaDB Version

MariaDB DateTime to Date

To convert the DateTime value to the date value in MariaDB, we need to use the Date() function. As the description says, it extracts the date part of the Date or DateTime expression as an expr value.

The syntax of the Date() function is given below:

SYNTAX:

DATE(expr)

The sample example of the Date() function is given below:

EXAMPLE:

SELECT DATE("2022-01-18 18:16:47") as Extract_Date;

As we see in the above query, we have used the date() function to extract date from a date and time content which is given by the user and shown the result as “2022-01-18” in the new column as “Extract_Date”.

Read: Replace Function in MariaDB

MariaDB DateTime with Timezone

In this section, we will discuss how to display a DateTime value with Timezone in MariaDB. To implement this task in MariaDB, we can use the MariaDB ADDTIME() function. Moreover, a sample example is shown below:

SELECT ADDTIME ("2016-10-04T23:00:00Z",1) AS NEW_TIME;

But, if we try to insert it with the INSERT statement in the usa_datetime table with DateTime value it will give an error. The sample example of the insertion of DateTime value in the usa_datetime table is given below:

INSERT INTO usa_datetime(D) VALUES ('2016-10-12T23:00:00Z');

By adding correct DateTime value in MariaDB by using the INSERT statement for usa_datetime table is given below:

INSERT INTO usa_datetime(D) VALUES ('2016-10-12T23:00:00Z');

SELECT * FROM usa_datetime;

In the previous version of MariaDB, it was used to do autocorrection for us. But after new update versions starting from 10.2.4, it doesn’t recognize your date format. It has changed the SQL mode to stricter. And now, it raises errors instead of giving warning signs for faulty Mysql queries.

[mysqlid]
sql mode= NO ENGINE SUBSTITUTION.NO AUTO CREATE USER

Well, we will suggest you change your query for MariaDB to fully understand it. For more info on SQL mode, please refer to the following page.

Also, check: MariaDB Vs SQLite – Key Differences

MariaDB DateTime Milliseconds

In MariaDB to show DateTime Milliseconds, we need to use Convert() function to show DateTime parameters as milliseconds in it.

DEFINITION:

The convert() function in MariaDB takes value from one type and produces a value to another datatype.

The syntax of the Convert() function is given below:

SYNTAX:

CONVERT(expr,type);

As the syntax says, the expr needs to be DateTime or date value and the type can be BINARY, CHAR, DATETIME, DATE, DOUBLE, etc.

The sample example of the convert() function is given below:

EXAMPLE:

SELECT CONVERT('2007-11-30 10:30:19',DATETIME(6)) AS New_Microsecond;

As the above image explains, we have used Convert() function to describe microseconds on the DateTime “2007-11-30 10:30:19” as expression expr and type as DateTime. So, by putting DateTime type as 6 value, it will show microseconds till 6 digit number as the highest decimal point.

If we try to add DateTime parameters is 60, it will show an error which is given below:

As the image tells, it will show SQL error (1426) for too big precision 60 specified for “2007-11-30 10:30:19” and the maximum length of milliseconds to show in output is 6 decimal digits.

Read: How to Create Function in MariaDB

MariaDB DateTime Default Current_Timestamp

In MariaDB, we are going to discuss CURRENT_TIMESTAMP() function which is the same related to the NOW() function. They both provide the current date and time value of the user’s computer while using their function.

The syntax of the CURRENT_TIMESTAMP() function is given below:

SYNTAX:

CURRENT_TIMESTAMP([precision])

The sample example of the CURRENT_TIMESTAMP() function is given below:

EXAMPLE:

SELECT CURRENT_TIMESTAMP();

As we see in the above query, using the current_timestamp() function will provide the current date and time of the user’s computer.

[Note]: If we want to add parameters by 2 inside the current_timestamp() function, it will increase the seconds by 2 and the result will also show nanoseconds in it which is “2022-01-18 14:27:44″.

The sample example of the current_timestamp() function is increased by 2 as it is shown below:

AFTER INCREASE:-

SELECT CURRENT_TIMESTAMP(2) AS current_time_increase;

As we see in the above query, by increasing the parameter by 2 of the current_timestamp() function. So, it has increased the seconds by 2 which changed and it also showed nanoseconds with it. Therefore it showed as “2022-01-18 14:59:58:63” inside new column name as “Current_Time_Increase”.

Read: How to Create Trigger in MariaDB

MariaDB DateTime to String

In MariaDB, to convert the DateTime value to the String value we need to use the date_format() function where parameters as now() and MariaDB format string as “%m%d%y” or “%d%m%Y”.

The sample example of the date_format() function is given below:

SELECT DATE_FORMAT(NOW(),'%Y%m%d') AS Date_to_String;

As we see in the above query, by using the date_format() function we have converted the current date value in string format by using “%Y%m%d”. So, the result is 20220118 in the new column as “Date_to_String”.

[NOTE]: If we try to separate the output value by year-wise as YYYY, month-wise as MM, and date-wise as DD. Therefore in the output, the YEAR is 2022, MONTH is 01 and DATE is 18.

Read: MariaDB Vs SQL Server – Detailed Comparison

MariaDB DateTime Difference

In this section, we will understand how to calculate the Datetime difference in MariaDB. However, to calculate the DateTime difference, we need to use the timestampdiff() function in the MariaDB.

DEFINITION: It return the difference between datetime_expr2 and datetime_expr1 like (datetime_expr2 – datetime_expr1) where datetime_expr1 and datetime_expr2 are date and DateTime expressions. One expression may be date and another expression may be DateTime.

The legal values of UNIT are the same who are listed in the description of the TIMESTAMPADD() function i.e; MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

It can also be used to calculate age. The syntax of the TIMESTAMPDIFF() function is given below:

SYNTAX:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

The sample example of the timestampdiff() function is given below:

EXAMPLE OF UNIT AS “MONTH”:

SELECT timestampdiff(MONTH,'1997-03-04','2022-01-18') as Total_Month;

As we see in the above query, by using timestampdiff() function the UNIT as MONTH, datetime_expr1 as 1997-03-04, and datetime_expr2 as 2022-01-18. It started calculating months from this date 2022-01-18 to 1997-03-04 and provided the result as 298 months in the new column name as Total_Month.

EXAMPLE UNIT AS YEAR:

The sample example of the timestampdiff() function by using UNIT as YEAR and shown the result below:

SELECT TIMESTAMPDIFF(YEAR,'1995-03-04','2022-01-18') AS Total_Year;

As we see in the above query, by using timestampdiff() function the UNIT as YEAR, datetime_expr1 as 1995-03-04, and datetime_expr2 as 2022-01-18. It started calculating months from this date 2022-01-18 to 1995-03-04 and provided the result as 26 years in the new column name as Total_Year.

EXAMPLE UNIT AS MINUTE:

The sample example of the timestampdiff() by using UNIT expression as MINUTE:

SELECT TIMESTAMPDIFF(MINUTE,'1995-03-04','2022-01-18') 
AS Total_Minutes;

As we see in the above query, by using timestampdiff() function the UNIT as MINUTE, datetime_expr1 as 1995-03-04, and datetime_expr2 as 2022-01-18. It started calculating months from this date 2022-01-18 to 1995-03-04 and provided the result as 14,136,180 minutes in the new column name as Total_Minutes.

NOTE: Just remember to calculate UNIT as SECOND, MICROSECOND, WEEK, DAY, or QUARTER. All we have to do is change the UNIT as these names, then it will calculate them as per their value, rest all the expressions and method procedure will be the same.

You may also like to read the following tutorials on MariaDB.

• MariaDB LIKE Operator [7 Examples]
• MariaDB Insert Into + Examples
• How to Remove User in MariaDB
• MariaDB Update Statement with Examples

In this tutorial, we have learned about the MariaDB Row_Number using some examples. Moreover, we have also covered the following topics in this tutorial.

• MariaDB DateTime
• MariaDB DateTime vs timestamp
• MariaDB DateTime Default Now
• MariaDB DateTime Now
• MariaDB DateTime to Date
• MariaDB DateTime with Timezone
• MariaDB DateTime Milliseconds
• MariaDB DateTime Default Current_Timestamp
• MariaDB DateTime to String
• MariaDB DateTime Difference