[MySQL] Unixtime / Date Type(형식) 변경하기

1. 현재시간 Unixtime값 구하기

mysql> select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

|       1474073079 |

+------------------+

1 row in set (0.00 sec)

 

2. Unixtime을 Date형식으로 변환

mysql> select from_unixtime(1474073079);

+---------------------------+

| from_unixtime(1474073079) |

+---------------------------+

| 2016-09-17 09:44:39       |

+---------------------------+

1 row in set (0.00 sec) 

 

mysql> select from_unixtime(1474073079, "%Y/%m/%d %H:%i:%S");

+------------------------------------------------+

| from_unixtime(1474073079, "%Y/%m/%d %H:%i:%S") |

+------------------------------------------------+

| 2016/09/17 09:44:39                            |

+------------------------------------------------+

1 row in set (0.00 sec)

 

3. Date값을 Unixtime으로 변환

mysql> select unix_timestamp('2016-09-17 11:11:11');

+---------------------------------------+

| unix_timestamp('2016-09-17 11:11:11') |

+---------------------------------------+

|                            1474078271 |

+---------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp('2016-09-17');

+------------------------------+

| unix_timestamp('2016-09-17') |

+------------------------------+

|                   1474038000 |

+------------------------------+

1 row in set (0.00 sec)