Cheat sheet n tweaks for MySQL

Interesting one.–
TIP 3:

Can the order of the columns in a create statement make a difference? YES

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the “automatically generated” time. So
if the record is updated, or inserted, this time gets changed. If the
order is changed, “timeEnter” is before “timeUpdate”, then, “timeEnter”
would get updated. First timestamp column updates automatically.

Note, in the table above timeEnter will only get updated if passed a null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

Hints: Need mm-dd-yyyy hh:mm:ss format?

select a,b,DATE_FORMAT(timeUpdate,’%m-%d-%Y %T’),DATE_FORMAT(timeEnter,’%m-%d-%Y %T’) from t;
+——+——+—————————————+————————————–+
| a | b | DATE_FORMAT(timeUpdate,’%m-%d-%Y %T’) | DATE_FORMAT(timeEnter,’%m-%d-%Y %T’) |
+——+——+—————————————+————————————–+
| 3 | 2 | 04-15-2004 19:14:36 | 04-15-2004 19:15:07 |
| 3 | 2 | 04-15-2004 19:14:39 | 04-15-2004 19:15:07 |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: