Monday, May 10, 2010

Mysql Timestamp a useful Data type

Till now we were using DateTime Datatpe but latly found that there is some thing better for tracking user updation and insertion tracking, say we use for security purpose time crate and time update there Timestamp can be best used as it automatically updates column without mentioning it , following is the example

mysql> CREATE TABLE ts_test1 (

-> ts1 TIMESTAMP,

-> ts2 TIMESTAMP,

-> data CHAR(30)

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> DESCRIBE ts_test1;

+——-+———–+——+—–+———————+——-+

| Field | Type | Null | Key | Default | Extra |

+——-+———–+——+—–+———————+——-+

| ts1 | timestamp | YES | | CURRENT_TIMESTAMP | |

| ts2 | timestamp | YES | | 0000-00-00 00:00:00 | |

| data | char(30) | YES | | NULL | |

+——-+———–+——+—–+———————+——-+

3 rows in set (0.01 sec)

mysql> INSERT INTO ts_test1 (data) VALUES (‘original_value’);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_test1;

+———————+———————+—————-+

| ts1 | ts2 | data |

+———————+———————+—————-+

| 2005-01-04 14:45:51 | 0000-00-00 00:00:00 | original_value |

+———————+———————+—————-+

1 row in set (0.00 sec)

mysql> . . . time passes . . .

mysql> UPDATE ts_test1 SET data=’updated_value’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM ts_test1;

+———————+———————+—————+

| ts1 | ts2 | data |

+———————+———————+—————+

| 2005-01-04 14:46:17 | 0000-00-00 00:00:00 | updated_value |

+———————+———————+—————+

1 row in set (0.00 sec)

The same behavior occurs if you specify both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP explicitly for the first TIMESTAMP column. It is also possible to use just one of the attributes. The following example uses DEFAULT CURRENT_TIMESTAMP, but omits ON UPDATE CURRENT_TIMESTAMP. The result is that the column is initialized automatically, but not updated when the record is updated:

mysql> CREATE TABLE ts_test2 (

-> created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

-> data CHAR(30)

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO ts_test2 (data) VALUES (‘original_value’);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ts_test2;

+———————+—————-+

| created_time | data |

+———————+—————-+

| 2005-01-04 14:46:39 | original_value |

+———————+—————-+

1 row in set (0.00 sec)

mysql> . . . time passes . . .

mysql> UPDATE ts_test2 SET data=’updated_value’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM ts_test2;

+———————+—————+

| created_time | data |

+———————+—————+

| 2005-01-04 14:46:39 | updated_value |

+———————+—————+

1 row in set (0.00 sec)

Note that even though the record is updated, the created_time column is not. In versions of MySQL Server before 4.1, the UPDATE statement would have caused the created_time column to be updated as well.

The next example demonstrates how to create a TIMESTAMP column that is not set to the current timestamp when the record is created, but only when it is updated. In this case, the column definition includes ON UPDATE CURRENT_TIMESTAMP but omits DEFAULT CURRENT_TIMESTAMP:

mysql> CREATE TABLE ts_test3 (

-> updated_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> data CHAR(30)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts_test3 (data) VALUES (‘original_value’);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_test3;

+———————+—————-+

| updated_time | data |

+———————+—————-+

| 0000-00-00 00:00:00 | original_value |

+———————+—————-+

1 row in set (0.00 sec)

mysql> UPDATE ts_test3 SET data=’updated_value’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM ts_test3;

+———————+—————+

| updated_time | data |

+———————+—————+

| 2005-01-04 14:47:10 | updated_value |

+———————+—————+

1 row in set (0.00 sec)

Note that you can choose to use CURRENT_TIMESTAMP with neither, either, or both of the attributes for a single TIMESTAMP column, but you cannot use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another:

mysql> CREATE TABLE ts_test4 (

-> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> data CHAR(30)

-> );

ERROR 1293 (HY000): Incorrect table definition; there can be

only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT

or ON UPDATE clause

Nevertheless, you can achieve the effect of having one column with the creation time and another with the time of the last update. To do this, create two TIMESTAMP columns. Define the column that should hold the creation time with DEFAULT 0 and explicitly set it to NULL whenever you INSERT a new record. Define the column that should hold the updated time with DEFAULT CURRENT_TIMESTAMP:

mysql> CREATE TABLE ts_test5 (

-> created TIMESTAMP DEFAULT 0,

-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> data CHAR(30)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts_test5 (created, data)

-> VALUES (NULL, ‘original_value’);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_test5;

+———————+———————+—————-+

| created | updated | data |

+———————+———————+—————-+

| 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value |

+———————+———————+—————-+

1 row in set (0.00 sec)

mysql> . . . time passes . . .

mysql> UPDATE ts_test5 SET data=’updated_value’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM ts_test5;

+———————+———————+—————+

| created | updated | data |

+———————+———————+—————+

| 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value |

+———————+———————+—————+

1 row in set (0.00 sec)

By default, MySQL defines TIMESTAMP columns as NOT NULL and stores the current timestamp in the column if you assign it a value of NULL. If you want to be able to store NULL in a TIMESTAMP column, you must explicitly write the column definition to allow NULL when creating or altering the column:

No comments:

Post a Comment