Displaying TIMESTAMP Values in Readable Form

     
mysql>
mysql> CREATE TABLE tsdemo2
    -> (
    ->     t_update TIMESTAMP, # record last-modification time
    ->     t_create TIMESTAMP, # record creation time
    ->     val INT
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO tsdemo2 (t_update,t_create,val) VALUES(NULL,NULL,5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tsdemo2;
+---------------------+---------------------+------+
| t_update            | t_create            | val  |
+---------------------+---------------------+------+
| 2011-10-03 13:05:53 | 2011-10-03 13:05:53 |    5 |
+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE tsdemo2 SET val = val + 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tsdemo2;
+---------------------+---------------------+------+
| t_update            | t_create            | val  |
+---------------------+---------------------+------+
| 2011-10-03 13:05:53 | 2011-10-03 13:05:53 |    6 |
+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT t_create, DATE_FORMAT(t_create,'%Y-%m-%d %T') FROM tsdemo2;
+---------------------+-------------------------------------+
| t_create            | DATE_FORMAT(t_create,'%Y-%m-%d %T') |
+---------------------+-------------------------------------+
| 2011-10-03 13:05:53 | 2011-10-03 13:05:53                 |
+---------------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table tsdemo2;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	The TIMESTAMP column type
2.	If you modify multiple records, the TIMESTAMP values in all of them will be updated:
3.	Table with TIMESTAMP data type
4.	Add Microsecond to time
5.	Records created from 1 PM to 4 PM:
6.	Records modified within the last 12 hours:

Displaying TIMESTAMP Values in Readable Form : TIMESTAMP « Date Time « SQL / MySQL

Related examples in the same category