Category Archives: MySQL

Difference Between DATE, DATETIME and TIMESTAMP data types

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

A major difference between these three data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime and date data type values are unchanged in relation to time zone.

Email, RSS Follow

Change column position in mysql table

Many times, we come across the situation where a column needs to be re-position in the table. Either at first position, at last position or  anywhere in the table after some columns. This particular modification can be done using ALTER TABLE queries.

#Position a column at first position
ALTER TABLE table_name MODIFY COLUMN column_name datatype FIRST;
Example : ALTER TABLE events MODIFY COLUMN event_id INT FIRST;
#Position a column after specific column
ALTER TABLE table_name MODIFY COLUMN column_name datatype AFTER another_column;
Example : ALTER TABLE events MODIFY COLUMN event_id INT AFTER event_name;
Email, RSS Follow