MariaDB Timestamp + Examples

In this MariaDB Tutorial, we will learn about the “MariaDB timestamp” and cover the following topics.

• MariaDB timestamp
• MariaDB timestamp to date
• MariaDB timestamp format
• MariaDB timestamp precision
• MariaDB timestamp on update
• MariaDB timestamp diff
• MariaDB timestamp now
• MariaDB timestamp comparison
• MariaDB timestamp add
• MariaDB group by and order by timestamp
• MariaDB current_timestamp vs now
• MariaDB current_timestamp milliseconds
• MariaDB timestamp column
• MariaDB timestamp epoch
• MariaDB extract date from timestamp
• MariaDB timestamp insert

MariaDB Timestamp

The MariaDB TIMESTAMP function returns the date or date-time expression as a date-time value with a single argument. When two arguments are provided to this function then it adds the second date-time expression to the first date-time expression and returns the result as a date-time value.

The syntax of the TIMESTAMP function is given below.

TIMESTAMP(expression)
----or--------
TIMESTAMP(expression_1,expression_2)

In the above syntax, we need to specify any Date or DateTime expression in place of expression and the function will return a DateTime value. Moreover, we can also specify multiple expressions for this function. And the function will add the given expressions and return the result.

Run the below query to know the use of TIMESTAMP with a single argument.

SELECT TIMESTAMP('2021-12-27');

In the above example, we are using the SELECT statement with the TIMESTAMP function for implementation. And we passed 2021-12-27 as an expression value to the TIMESTAMP function. The function will convert this date expression into the DateTime value. In the end, we will get the following DateTime value as result.

Again run the query with time (for two-argument).

Now, other than a function, we can also use the TIMESTAMP field in a MariaDB table. So, just like defining a table column with a data type, we can also define the TIMESTAMP column in MariaDB.

Moreover, a DateTime value will be automatically added to the TIMESTAMP column based on the insertion of a record. Let’s understand this concept using an example in MariaDB.

CREATE TABLE emp_table (
	emp_id VARCHAR(50),
	joining_time TIMESTAMP,
	dept_name VARCHAR(50),
	dept_loc VARCHAR(50)
);

In the above query, we are using CREATE TABLE statement in MariaDB to create a table named emp_table. And for this table, we have defined 4 columns out of which joining_time is a TIMESTAMP column.

Next, we will insert some rows within the emp_table using the following SQL query.

INSERT INTO emp_table (emp_id, dept_name, dept_loc) 
VALUES ('23-7347776', 'Legal', 'United States'),
('11-5921262', 'Support', 'United States'),
('57-4446963', 'Human Resources', 'Canada');

In the above code, you can easily observe that we haven’t defined the values for the joining_time field. This is because the joining_time is the TIMESTAMP field and its value will be automatically added on each successful insert. We can also confirm it by using the SELECT statement in MariaDB.

Also, check: How to install MariaDB + Uninstallation steps

MariaDB timestamp to date

In the MariaDB, the timestamp can be converted into a date using the CONVERT function which takes two argument expressions and types.

The syntax of the CONVERT is given below.

CONVERT(expression,data_type);

Where data_type can be one of the following.

• DATETIME
• DECIMAL[(M[,D])]
• DOUBLE
• BINARY
• CHAR
• DATE
• FLOAT
• INTEGER
• Short for SIGNED INTEGER
• SIGNED [INTEGER]
• UNSIGNED [INTEGER]
• TIME
• VARCHAR

Let’s convert the timestamp into a date.

SELECT CONVERT(TIMESTAMP('2021-12-27 12:00:00','5:30:00'),DATE);

In the above code, we are converting the timestamp into a date using the convert function.

Read: How To Check MariaDB Version

MariaDB timestamp format

The TIMESTAMP function in MariaDB can take a date value in a different format in the MariaDB. But the database stores DateTime in only one format YYYY-MM-DD.

Let’s input the date-time in a different format to the function TIMESTAMP.

SELECT TIMESTAMP('2021-02-23');

SELECT TIMESTAMP('2021/02/23');

SELECT TIMESTAMP('2021*02*23');

SELECT TIMESTAMP('1999@01@12');

SELECT TIMESTAMP('1999#01#12');

In the above code, we are inputting the date in a different format, as we can see in each query, the date is separated with the different symbols such as a hyphen(-), slash(/), the asterisk(*), etc.

From the above output, we got the DateTime value in a different format using the TIMESTAMP function.

Read: How to create a user in MariaDB

MariaDB timestamp precision

In MariaDB, a precision represents the microsecond, when we want to show or use the microsecond in the MariaDB, then we provide the precision parameter to the TIMESTAMP function.

The syntax is given below.

TIMESTAMP [(<microsecond precision)]

The format for the timestamp in MariaDB is YYYY-MM-DD HH:MM:SS.ffffff. Where ffffff represents the microsecond that we get using the precision, the range of this precision is between 0 and 6.

Let’s create the timestamp with precision using the below query.

SELECT TIMESTAMP('2021-05-23 10:13:22.345');

In the above code, after the dot, we have provided the precision (microseconds) of 3 digits as 345.

Read MariaDB Substring

MariaDB timestamp on update

The MariaDB TIMESTAMP can be changed while updating the table using the ON UPDATE statement. Suppose you are a database developer in the ‘xyz‘ company of the United States of America, and you have to create a table of employees that keeps a record of employee login and logout timing.

With help of the ON UPDATE keyword, the column is defined with a timestamp. So whenever employees log in and log out, it will update the timestamp automatically.

Let’s create a table as demo_update.

CREATE TABLE demo_update(id INT,date_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NAME VARCHAR(20), country VARCHAR(30));

Here, we are creating the table as demo_update with columns id, date_time, name, and country of type INT, TIMESTAMP, VARCHAR(20), and VARCHAR(30) respectively.

Insert the following records and view the table.

INSERT INTO demo_update(id,date_time,NAME,country)VALUES(1,'2021-01-12 12:56:11','Jhon','United States'),
(2,'2020-03-12 09:23:30','James','Canada');

SELECT * FROM demo_update;

As we can see in the above output, let’s change the “James” country from Canada to United Kindom and check the column date_time.

UPDATE demo_update
SET country = 'United Kindom'
WHERE id = 2;

As we can see the date_time column value of James changed as the country column value is updated from Canada to United Kindom.

Read: How to Create Database in MariaDB

MariaDB timestamp diff

The MariaDB TIMESTAMPDIFF function returns the difference between 2 DateTime expressions. For example, datetime_expression2 – datetime_expression1, where datetime_expression1 and datetime_expression2 are date or DateTime expressions.

However, one expression can be a date and the other a DateTime in MariaDB. And we can also get the result in any of the timestamp formats.

The syntax of using the TIMESTAMPDIFF function in MariaDB is given below.

TIMESTAMPDIFF(unit,datetime_expression1,datetime_expression2)

In the syntax, the unit argument is utilized to specify the units in which we want the result. The legal values for the unit arguments are the MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Let’s understand the execution of the above syntax using an example in MariaDB.

SELECT TIMESTAMPDIFF(MONTH,'2010-02-01','2021-05-01');

In the above code, we are calculating the timestamp difference between ‘2010-02-01’ and ‘2021-05-01’ in months. But we have only provided a date to understand it better.

The above output shows 135 months between ‘2010-02-01’ and ‘2021-05-01’ date values.

MariaDB timestamp now

The MariaDB NOW function returns the current date and time as ‘YYYY-MM-DD HH:MM:SS.uuuu’ format, which depends on how the function is used in a string or numeric text. The NOW function returns the value in the current time zone.

The syntax is given below.

NOW(precision);

First, set the time zone to America/New York

SET time_zone = 'America/New_York';

then run the now function to know the current date-time.

SELECT NOW();

Read: How to Grant User Access to a MariaDB Database

MariaDB timestamp comparison

In MariaDB, we can compare the timestamp using the DATE function, so let’s create a new table as comparison_demo.

CREATE TABLE comparison_demo
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Result_declareDate timestamp
   );

Insert the following records and view the table.

INSERT INTO comparison_demo(Result_declareDate)VALUES('2020-03-31'),('2020-04-15'),('2020-04-10'),('2020-03-29'),('2020-04-07');


SELECT * FROM comparison_demo;

Now compare the timestamp using the below query.

SELECT DATE(`Result_declareDate`) FROM comparison_demo WHERE
DATE(`Result_declareDate`) < CURDATE() - INTERVAL 3 DAY;

Read: How to Create Table in MariaDB

MariaDB timestamp add

The MariaDB TIMESTAMPADD function sums up the integer(numeric) expression intervals to the date or date-time expression. The units for the interval are based on the unit provided as an argument, which can be one of the following values: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The syntax of TIMESTAMPADD is given below.

TIMESTAMPADD(unit,interval,datetime_expression)

In the above syntax, the datetime_expression is utilized to specify either a DateTime or date value to which we want to add some intervals. The interval is utilized to specify the interval value in integers. And the unit parameter in the TIMESTAMPADD function is utilized to specify the unit for an interval. Now, this unit can either be one of the followings.

We can under the use of the above better by executing an example. Let’s add 10 minutes to a date-time value.

SELECT TIMESTAMPADD(MINUTE,10,'2021-12-27');

In the above example, we specified the unit as MINUTE, the interval as 10, and the datetime_expression as a date value i.e 2021-12-27. So, the query will add 10 minutes to 2021-12-27 and return “2021-12-27 00:10:00” as the final result.

Read MariaDB Full Outer Join

MariaDB group by and order by timestamp

In MariaDB, the timestamp can be grouped or ordered by using a simple statement or query. Let’s understand its usability using an example. For this, consider the following table.

The table here we are going to use is the sales table that contains the information about the products like vegetables, cereal, fruits, etc, in the country like United Kindom, Canada, etc.

The downloadable link is given for the datasets or sales files that you can load in your database and perform some of the queries.

Download

The snapshot of the sales table is given below.

Look at the above table output, especially at the columns Order Date and Ship Date, the columns don’t have time, it has only date. So, here, we will consider this column as timestamp, although it contains the date, whatever the query is performed on these columns, that can also be applied to timestamp.

Let’s group or order the timestamp in the table sales.

SELECT country,`Item Type`,`Order Date` FROM sales
GROUP BY `Order Date`
ORDER BY `Item Type`,`Order Date`;

In the above code, we are selecting the columns country, Item Type, Order Date from the sales table using the SELECT statement. Then grouping and ordering it by Order Date and Item Type, Order Date respectively.

Read: How to Add Column in MariaDB

MariaDB current_timestamp vs now

In MariaDB, CURRENT_TIMESTAMP is equivalent to NOW both functions return the current date and date-time as a value. The difference is that the current_timestamp can be used without parenthesis whereas now is used with parenthesis.

Let’s see the use of current_timestamp and now functions.

SELECT CURRENT_TIMESTAMP;

SELECT NOW();

MariaDB current_timestamp milliseconds

The MariaDB CURRENT_TIMESTAMP function can show the date-time with seconds, to get milliseconds, we will need to calculate the milliseconds by multiplying 1000 with currrent_timestamp (later below we will convert the current_timestamp into seconds).

The syntax of CURRENT_TIMESTAMP is given below.

CURRENT_TIMESTAMP(precision);

First, we will check with on current_timestamp or without precision parameter.

SELECT CURRENT_TIMESTAMP;

As we can see in the above output, we got time in HH:MM:SS (till seconds), and current_timestamp cannot provide the time in millisecond but it can show in a microsecond with the help of precision parameter.

As we talked about before, we will convert the timestamp into seconds and multiply seconds with 1000 to get the milliseconds.

To convert the current_timestamp into seconds, we are going to use the function UNIX_TIMESTAMP which returns time in seconds.

Let’s pass the current_timestamp to unix_timestamp and multiply it by 1000 to get the milliseconds.

SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP())*1000;

Read: How to Drop Column from MariaDB Table

MariaDB timestamp column

Here we will create the column of type timestamp means this column will contain the date and time parts in MariaDB. To make a column to store the date-time, we will define this column as timestamp or the data type of the column will be timestamp.

Create the table with a column of type timestamp.

CREATE TABLE time_stamp(id INT ,date_time TIMESTAMP);

View the columns data type of the table time_stamp using the below code.

SHOW COLUMNS FROM time_stamp;

MariaDB timestamp epoch

In MariaDB, an epoch is considered as a second, here we will use the EXTRACT function to extract the epoch part of the timestamp. The EXTRACT function accepts two arguments, the first is the unit of time and the second is the timestamp.

The syntax of EXTRACT is given below.

EXTRACT(unit_time FROM date);

Let’s extract the epoch from a timestamp.

SELECT EXTRACT(SECOND FROM '2021-02-12 10:30:45');

In the above code, we have provided the unit_time as a second to the EXTRACT function to get the second from the timestamp.

The output shows that the extracted second from the timestamp is 45.

Read How to Create Trigger in MariaDB

MariaDB extract date from timestamp

You already know about the MariaDB EXTRACT function that extracts the required unit of time from a timestamp, but here we will use another function that extracts the date part from a timestamp and that is the DATE function.

The syntax of the DATE function is given below.

DATE(expression) - expression represents the date or timestamp

Now extract the date from a timestamp using the below query.

SELECT DATE('2021-02-12 10:30:45');

The DATE function in the above code will convert the timestamp into date only. The result of the above code can be seen in the below output.

MariaDB timestamp insert

In the above sub-section ‘MariaDB timestamp column” we have created the table with a column of type timestamp. That table we will use here to insert the records in the table.

Insert the following records in that table.

CREATE TABLE time_stamp(id INT ,date_time TIMESTAMP);

INSERT INTO time_stamp(id,date_time)VALUES(1,'2021-02-12 09:55:10'),
(2,'2021-06-12 03:53:10'),(3,'2021-11-12 11:14:25'),(4,'2021-08-23 12:55:20');

SELECT * FROM time_stamp;

In the above output, look at line number 1 where the table is created as time_stamp with columns id, date_time of type INT, and TIMESTAMP respectively.

Inline numbers 3 and 4, values are inserted into the table time_stamp using the INSERT INTO statement. Here, we are inserting timestamp values.

You may also like to read the following MariaDB tutorials.

• How to Create Function in MariaDB
• MariaDB ERROR 1064

So, in this tutorial, we have learned the “MariaDB timestamp” and also covered the following topics.

• MariaDB timestamp
• MariaDB timestamp to date
• MariaDB timestamp format
• MariaDB timestamp precision
• MariaDB timestamp on update
• MariaDB timestamp diff
• MariaDB timestamp now
• MariaDB timestamp comparison
• MariaDB timestamp add
• MariaDB group by and order by timestamp
• MariaDB current_timestamp vs now
• MariaDB current_timestamp milliseconds
• MariaDB timestamp column
• MariaDB timestamp epoch
• MariaDB extract date from timestamp
• MariaDB timestamp insert