MySQL bigint类型和datetime类型的转换

原文地址：http://blog.csdn.net/jphaoren/article/details/7280392

1、bigint类型转换为datetime类型

• -- 假设 1164691264437 是 Java 里的“日期时间”：即：自1970-01-01 00:00:00以来的毫秒数
• /*
• getTime
• public long getTime()Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Date object.
• Returns:
• the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this date.
• */
• 
  
• mysql> select from_unixtime(1164691264437/1000);
• +-----------------------------------+
• | from_unixtime(1164691264437/1000) |
• +-----------------------------------+
• | 2006-11-28 05:21:04               |
• +-----------------------------------+
  

2、datetime类型转换为bigint类型

• -- 假设 "2011-05-31 23:59:59" 是 Java 里的“日期时间”：即：自1970-01-01 00:00:00以来的毫秒数
• mysql> select UNIX_TIMESTAMP('2011-05-31 23:59:59');
• +---------------------------------------+
• | UNIX_TIMESTAMP('2011-05-31 23:59:59') |
• +---------------------------------------+
• |                            1306886399 |
• +---------------------------------------+