c++ mysql ctime_mysql中 常用的时间函数

时间函数：

now：当前时间带格式

mysql> select now();

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

| now() |

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

| 2015-09-06 10:47:33 |

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

1 row in set (0.00 sec)

to_days：给定一个日期date, 返回一个天数 (从年份0开始的天数 )。

mysql> select TO_DAYS(NOW());

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

| TO_DAYS(NOW()) |

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

| 736212 |

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

1 row in set (0.00 sec)

今天插入的条数：

mysql> select count(*) from black_list where to_days(now()) - to_days(ctime) <1;

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

| count(*) |

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

| 38089 |

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

1 row in set (1.93 sec)

年数函数：

天数函数：

星期函数：

今天星期几：

DAYOFWEEK 1=》星期日；2=》星期一；3=》星期二。。。7=》星期六

mysql> select DAYOFWEEK(now());

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

| DAYOFWEEK(now()) |

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

| 1 |

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

1 row in set (0.00 sec)

weekday  0=》星期一；1=》星期二；2=》星期三。。。6=》星期日

mysql> select weekday(now());

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

| weekday(now()) |

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

| 6 |

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

1 row in set (0.00 sec)

今天几号：dayofmonth：

mysql> select dayofmonth(now());

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

| dayofmonth(now()) |

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

| 6 |

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

1 row in set (0.00 sec)

今天是今年中的第几天：dayofyear

mysql> select dayofyear(now());

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

| dayofyear(now()) |

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

| 249 |

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

1 row in set (0.00 sec)

查询月份：

mysql> select month(now());

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

| month(now()) |

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

| 9 |

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

1 row in set (0.00 sec)

一年中的第几周(从0开始)有第二个参数1就从1开始：week(now(),1)

mysql> select week("2015-01-10");

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

| week("2015-01-10") |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select week("2015-01-11");

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

| week("2015-01-11") |

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

| 2 |

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

1 row in set (0.00 sec)

mysql> select week("2015-01-1");

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

| week("2015-01-1") |

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

| 0 |

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

星期几的英文：

mysql> select dayname(now());

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

| dayname(now()) |

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

| Sunday |

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

1 row in set (0.00 sec)

月份的英文

mysql> select monthname(now());

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

| monthname(now()) |

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

| September |

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

1 row in set (0.00 sec)

季度(1-4)

mysql> select QUARTER(now());

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

| QUARTER(now()) |

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

| 3 |

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

1 row in set (0.00 sec)

年月日时分秒：

mysql> select year(now());

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

| year(now()) |

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

| 2015 |

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

1 row in set (0.00 sec)

mysql> select month(now());

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

| month(now()) |

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

| 9 |

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

1 row in set (0.00 sec)

mysql> select day(now());

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

| day(now()) |

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

| 6 |

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

1 row in set (0.00 sec)

mysql> select hour(now());

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

| hour(now()) |

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

| 11 |

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

1 row in set (0.00 sec)

mysql> select minute(now());

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

| minute(now()) |

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

| 41 |

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

1 row in set (0.00 sec)

mysql> select second(now());

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

| second(now()) |

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

| 20 |

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

1 row in set (0.00 sec)

增加日期：第一个参数时间(2015-09-06 11:46:37)，第二个参数 Interval 2 year 、Interval 2 week、Interval 2 day、Interval 2 hour、minute、second

mysql> select DATE_ADD(now(), Interval 2 year);

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

| DATE_ADD(now(), Interval 2 year) |

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

| 2017-09-06 11:46:37 |

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

1 row in set (0.00 sec)

日期格式化：

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday )

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

mysql> select date_format(now(),"%M");

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

| date_format(now(),"%M") |

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

| September |

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

1 row in set (0.00 sec)

mysql> select date_format(now(),"%m");

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

| date_format(now(),"%m") |

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

| 09 |

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

1 row in set (0.00 sec)

mysql> select date_format(now(),"%D");

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

| date_format(now(),"%D") |

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

| 6th |

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

1 row in set (0.00 sec)

mysql> select date_format(now(),"%d");

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

| date_format(now(),"%d") |

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

| 06 |

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

1 row in set (0.00 sec)

mysql> select date_format("2015-8-28","%d");

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

| date_format("2015-8-28","%d") |

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

| 28 |

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

1 row in set (0.00 sec)

mysql> select date_format("2015-8-28","%m-%d");

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

| date_format("2015-8-28","%m-%d") |

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

| 08-28 |

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

1 row in set (0.00 sec)

(待续)

原文：http://www.cnblogs.com/dzlffmx/p/4784846.html