mysql 时间戳 本帖最后由 baiyuxiong 于 2011-01-13 12:37:37 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 用curdate()即可。mysql> create table ttt(id timestamp);Query OK, 0 rows affected (0.16 sec)mysql> insert into ttt values(now());Query OK, 1 row affected (0.05 sec)mysql> select * from ttt;+---------------------+| id |+---------------------+| 2011-01-13 12:45:08 |+---------------------+1 row in set (0.00 sec)mysql> insert into ttt values(curdate());Query OK, 1 row affected (0.06 sec)mysql> select * from ttt;+---------------------+| id |+---------------------+| 2011-01-13 12:45:08 || 2011-01-13 00:00:00 |+---------------------+2 rows in set (0.00 sec)mysql> UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) 返回一个Unix时间戳(从'1970-01-01 00:00:00'GMT开始的秒数,date默认值为当前时间)mysql> select UNIX_TIMESTAMP(); -> 882226357 mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580 FROM_UNIXTIME(unix_timestamp) 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回时间戳的值(根据返回值所处上下文是字符串或数字) mysql> select FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00' mysql> select FROM_UNIXTIME(875996580) + 0; -> 19971004222300 FROM_UNIXTIME(unix_timestamp,format) 以format字符串格式返回时间戳的值mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x'); -> '1997 23rd December 03:43:30 x' mysql> select date_format(now(),'%Y-%m-%d 00:00:00') as ttt;+---------------------+| ttt |+---------------------+| 2011-01-13 00:00:00 |+---------------------+ mysql> select curdate();+------------+| curdate() |+------------+| 2011-01-13 |+------------+1 row in set (0.00 sec)mysql> select UNIX_TIMESTAMP(curdate());+---------------------------+| UNIX_TIMESTAMP(curdate()) |+---------------------------+| 1294848000 |+---------------------------+1 row in set (0.08 sec)mysql> 跪求啥原因,运行后出现以下Exception mysql左连接的问题 菜鸟请教高手:.txt形式的数据如何导入到Mysql数据库中 关于mysql中BETWEEN函数的使用疑惑 null问题! 建立 AUTO_INCREMENT字段,但是不然它是主键是否有办法? 大家看看我这个SQL查询如何优化呢?跪求能人。 关于MYSQL数据库字符集的问题 mysql字段数目问题! mysql自带的mysqlslap怎么做并发测试 更新语句怎么写 mysql-proxy安装,编译全部出错,configure时出错,求高手
mysql> create table ttt(id timestamp);
Query OK, 0 rows affected (0.16 sec)mysql> insert into ttt values(now());
Query OK, 1 row affected (0.05 sec)mysql> select * from ttt;
+---------------------+
| id |
+---------------------+
| 2011-01-13 12:45:08 |
+---------------------+
1 row in set (0.00 sec)mysql> insert into ttt values(curdate());
Query OK, 1 row affected (0.06 sec)mysql> select * from ttt;
+---------------------+
| id |
+---------------------+
| 2011-01-13 12:45:08 |
| 2011-01-13 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)mysql>
UNIX_TIMESTAMP(date)
返回一个Unix时间戳(从'1970-01-01
00:00:00'GMT开始的秒数,date默认值为当前时间)
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04
22:23:00');
-> 875996580
FROM_UNIXTIME(unix_timestamp)
以'YYYY-MM-DD
HH:MM:SS'或YYYYMMDDHHMMSS格式返回时间戳的值(根据返回值所处上下文是字符串或数字)
mysql> select
FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql>
select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
以format字符串格式返回时间戳的值
mysql>
select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
->
'1997 23rd December 03:43:30 x'
+---------------------+
| ttt |
+---------------------+
| 2011-01-13 00:00:00 |
+---------------------+
+------------+
| curdate() |
+------------+
| 2011-01-13 |
+------------+
1 row in set (0.00 sec)mysql> select UNIX_TIMESTAMP(curdate());
+---------------------------+
| UNIX_TIMESTAMP(curdate()) |
+---------------------------+
| 1294848000 |
+---------------------------+
1 row in set (0.08 sec)mysql>