|
24534,-2);
+----------------------+-------------------------+
| truncate(903.2432,2) | truncate(902.324534,-2) |
+----------------------+-------------------------+
| 903.24 | 900 |
+----------------------+-------------------------+
日期和时间函数
curdate()
curtime()
now()
unix_timestamp(date)
from_uinxtime()
week(date)
year(date)
hour(time)
minute(time)
monthname(date)
1、获取当前日期和时间
now(),current_timestamp(),localtime(),sysdate();
推荐使用now()
mysql> select now() as 'now',current_timestamp() as 'current_timestamp',localtime() as 'localtime',sysdate() as 'sysdate';
+---------------------+---------------------+---------------------+---------------------+
| now | current_timestamp | localtime | sysdate |
+---------------------+---------------------+---------------------+---------------------+
| 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 |
+---------------------+---------------------+---------------------+---------------------+
2、获取当前日期
curdate(),current_date()
推荐使用curdate()
mysql> select current_date() as 'current_date',curdate() as 'curdate';
+--------------+------------+
| current_date | curdate |
+--------------+------------+
| 2015-04-29 | 2015-04-29 |
+--------------+------------+
3、获取当前时间
curtime()和current_time()
推荐使用curtime()
mysql> select current_time() as 'current_time',curtime() as 'curtime';
+--------------+----------+
| current_time | curtime |
+--------------+----------+
| 07:00:27 | 07:00:27 |
+--------------+----------+
4、通过各种方式显示日期和时间
(1)unix方式显示
unix_timestamp():返回时间戳格式的时间
from_unixtime():将时间戳格式时间转换成普通格式的时间
mysql> select now() as '当前时间',unix_timestamp(now()) 'unix格式',from_unixtime(unix_timestamp(now())) as '普通格式';
+---------------------+------------+---------------------+
| 当前时间 | unix格式 | 普通格式 |
+---------------------+------------+---------------------+
| 2015-04-30 07:04:21 | 1430348661 | 2015-04-30 07:04:21 |
+---------------------+------------+---------------------+
注意：unix_timestamp()函数没有参数传入，则会显示出当前时间和日期的时间戳形式，如果传入了某个时间参数，则会显示所传入时间的时间戳。
(2)通过UTC方式显示日期和时间
UTC，即国际协调时间
utc_date()：实现日期
utc_time()：实现时间
mysql> select now() as 'now',utc_date() as 'utc date',utc_time() as 'utc time';
+---------------------+------------+----------+
| now | utc date | utc time |
+---------------------+------------+----------+
| 2015-04-30 07:14:49 | 2015-04-29 | 23:14:49 |
+---------------------+------------+----------+
注意：返回的时间与现在的时间有8小时之差
5、获取日期和时间各部分值
year() :日期的年份
quarter() :日期所属第几个季度
month() :月
week() :日期属于第几个星期
dayofmonth() :属于当前月的第几天
hour() :时间的小时
minute() :分钟
second() :秒
mysql> select
-> now() as 'now',
-> quarter(now()) as 'quarter',
-> month(now()) as 'month',
-> week(now()) as 'week',
-> dayofmonth(now()) as 'day',
-> hour(now()) as 'hour',
-> minute(now()) as 'minute',
-> second(now()) as 'second';
+---------------------+---------+-------+------+------+------+--------+--------+
| now | quarter | month | week | day | hour | minute | second |
+---------------------+---------+-------+------+------+------+--------+--------+
| 2015-04-30 07:29:19 | 2 | 4 | 17 | 30 | 7 | 29 | 19 |
+---------------------+---------+-------+------+------+------+--------+--------+
1、月
month( |