MySQL - TIMESTAMP() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL TIMESTAMP() function is converts the date or datetime expression as a datetime value and returns the result in the form o f a string.

You can also pass the a second argument which representing a time expression. If you do so the specified time expression is added to the date given.

Syntax

Following is the syntax of the above function –

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

Where, expr is the date-time or the time expression from which you need to get the timestamp.

Example 1

Following example demonstrates the usage of the TIMESTAMP() function –

mysql> SELECT TIMESTAMP('1078:06:23');
+-------------------------+
| TIMESTAMP('1078:06:23') |
+-------------------------+
| 1078-06-23 00:00:00     |
+-------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT TIMESTAMP('2012:11:01');
+-------------------------+
| TIMESTAMP('2012:11:01') |
+-------------------------+
| 2012-11-01 00:00:00     |
+-------------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT TIMESTAMP('2015-09-05 09:40:45.2300');
+---------------------------------------+
| TIMESTAMP('2015-09-05 09:40:45.2300') |
+---------------------------------------+
| 2015-09-05 09:40:45.2300              |
+---------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are passing the second parameter (time value) to this function –

mysql> SELECT TIMESTAMP('1986:06:26', '12:45:38');
+-------------------------------------+
| TIMESTAMP('1986:06:26', '12:45:38') |
+-------------------------------------+
| 1986-06-26 12:45:38                 |
+-------------------------------------+
1 row in set (0.00 sec)

Example 5

In the following example we are adding a time value to the current timestamp —

mysql> SELECT TIMESTAMP(CURRENT_TIMESTAMP, '12:12:12');
+------------------------------------------+
| TIMESTAMP(CURRENT_TIMESTAMP, '12:12:12') |
+------------------------------------------+
| 2021-07-15 11:24:15                      |
+------------------------------------------+
1 row in set (0.00 sec)

Example 6

In the following example we are passing the result of the CURTIME() function as the second argument to this function –

mysql> SELECT TIMESTAMP('1986:06:26', CURTIME());
+------------------------------------+
| TIMESTAMP('1986:06:26', CURTIME()) |
+------------------------------------+
| 1986-06-26 23:10:00                |
+------------------------------------+
1 row in set (0.00 sec)

Example 7

We can pass the result of the NOW() function as an argument to this function –

mysql> SELECT TIMESTAMP(NOW());
+---------------------+
| TIMESTAMP(NOW())    |
+---------------------+
| 2021-07-14 23:12:26 |
+---------------------+
1 row in set (0.00 sec)

Example 8

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query displays the entities in the Date_Of_Birth column as datetime values —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, TIMESTAMP(Date_Of_Birth) FROM MyPlayers;
+------------+------------+---------------+-------------+--------------------------+
| First_Name |  Last_Name | Date_Of_Birth | Country     | TIMESTAMP(Date_Of_Birth) |
+------------+------------+---------------+-------------+--------------------------+
| Shikhar    | Dhawan     | 1981-12-05    | India       | 1981-12-05 00:00:00      |
| Jonathan   | Trott      | 1981-04-22    | SouthAfrica | 1981-04-22 00:00:00      |
| Kumara     | Sangakkara | 1977-10-27    | Srilanka    | 1977-10-27 00:00:00      |
| Virat      | Kohli      | 1988-11-05    | India       | 1988-11-05 00:00:00      |
| Rohit      | Sharma     | 1987-04-30    | India       | 1987-04-30 00:00:00      |
| Ravindra   | Jadeja     | 1988-12-06    | India       | 1988-12-06 00:00:00      |
| James      | Anderson   | 1982-06-30    | England     | 1982-06-30 00:00:00      |
+------------+------------+---------------+-------------+--------------------------+
7 rows in set (0.16 sec)

Example 7

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query adds the DispatchTime to the DispatchDate values and displays them as a single timestamp —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price, TIMESTAMP(DispatchDate, DispatchTime) as Timestamp FROM sales;
+-------------+--------------+--------------+-------+---------------------+
| ProductName | CustomerName | DispatchDate | Price |           Timestamp |
+-------------+--------------+--------------+-------+---------------------+
| Key-Board   | Raja         | 2019-09-01   | 7000  | 2019-09-01 11:00:00 |
| Earphones   | Roja         | 2019-05-01   | 2000  | 2019-05-01 11:00:00 |
| Mouse       | Puja         | 2019-03-01   | 3000  | 2019-03-01 10:59:59 |
| Mobile      | Vanaja       | 2019-03-01   | 9000  | 2019-03-01 10:10:52 |
| Headset     | Jalaja       | 2019-04-06   | 6000  | 2019-04-06 11:08:59 |
+-------------+--------------+--------------+-------+---------------------+
5 rows in set (0.00 sec)

Example 8

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date,
	SubscriptionTime time
);
insert into SubscribersData values('Raja', 'Premium', Date('2020-10-21'), Time('20:53:49'));
insert into SubscribersData values('Roja', 'Basic', Date('2020-11-26'), Time('10:13:19'));
insert into SubscribersData values('Puja', 'Moderate', Date('2021-03-07'), Time('05:43:20'));
insert into SubscribersData values('Vanaja', 'Basic', Date('2021-02-21'), Time('16:36:39'));
insert into SubscribersData values('Jalaja', 'Premium', Date('2021-01-30'), Time('12:45:45'));

Following query displays the values of the columns SubscriptionDate, SubscriptionTime as a single column SubscriptionTimestamp –

mysql> SELECT SubscriberName, PackageName, SubscriptionDate, SubscriptionTime, TIMESTAMP(SubscriptionDate, SubscriptionTime) as SubscriptionTimestamp FROM SubscribersData;
+----------------+-------------+------------------+------------------+-----------------------+
| SubscriberName | PackageName | SubscriptionDate | SubscriptionTime | SubscriptionTimestamp |
+----------------+-------------+------------------+------------------+-----------------------+
| Raja           | Premium     | 2020-10-21       | 20:53:49         | 2020-10-21 20:53:49   |
| Roja           | Basic       | 2020-11-26       | 10:13:19         | 2020-11-26 10:13:19   |
| Puja           | Moderate    | 2021-03-07       | 05:43:20         | 2021-03-07 05:43:20   |
| Vanaja         | Basic       | 2021-02-21       | 16:36:39         | 2021-02-21 16:36:39   |
| Jalaja         | Premium     | 2021-01-30       | 12:45:45         | 2021-01-30 12:45:45   |
+----------------+-------------+------------------+------------------+-----------------------+
5 rows in set (0.13 sec)