mysql日期处理函数,PHP模拟SQLServer的两个日期处理函数。//在PHP中管理日期非常不实惠,比方求七个日子之间离开的月度?该咋办吧? 
//文件名:date.inc.php3 
//在选取那三个函数前,要先将日期或日期时间更动来timestamp类型。 
//如: 
//$today=mktime(0,0,0,date(“m”),date(“d”),date(“Y”)); 

//在PHP中拍卖日期特不便利,比如求七个日子之间距离的月度?该怎么办呢?
  //文件名:date.inc.php3
  //在行使那五个函数前,要先将日期或日期时间更动到timestamp类型。
  //如:
  //$today=mktime(0,0,0,date(“m”),date(“d”),date(“Y”));

//在PHP中拍卖日期特别不方便人民群众,比如求七个日子之间相差的月度?该怎么做呢? 
  //文件名:date.inc.php3 
  //在接纳那三个函数前,要先将日期或日期时间更改来timestamp类型。 
  //如: 
  //$today=mktime(0,0,0,date(”m”),date(”d”),date(”Y”)); 
  /****模拟sqlserver中的dateadd函数******* 
  $part 类型:string 
  取值范围:year,month,day,hour,min,sec 
  表示:要加进的日期的哪些部分 
  $n 类型:数值 
  表示:要增扩张少,依照$part决定追加哪些部分 
  可为负数 
  $datetime类型:timestamp 
  代表:扩充的基数 
  返回 类型:timestamp 

mysql 日期时间处理函数,mysql日期管理函数

  • ADDDATE(date,interval expr unit),ADDDATE(expr,days) —
    增多流年值(时区)为日期值,其为DATE_ADD()的别名

DATA_ADD(date,INTERVAL expr unit)、DATE_SUB(date,INTERVAL expr unit)

这一个函数进行时间运算;date参数钦赐开首日期的日子或日期时间值,expr是二个表明式钦赐要拉长或减弱的值的距离的初始日期

unit Value Expected expr Format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'

 

也能够编写

date + INTERVAL expr unit
date - INTERVAL expr unit

 

mysql> select '2008-1-31' + interval 1 month;
+--------------------------------+
| '2008-1-31' + interval 1 month |
+--------------------------------+
| 2008-02-29                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2008-1-31',interval 1 month);
+----------------------------------------+
| date_add('2008-1-31',interval 1 month) |
+----------------------------------------+
| 2008-02-29                             |
+----------------------------------------+
1 row in set (0.00 sec)

 

收获一天的开端和结尾的年月

mysql> select '2015-4-4 0:0:0' + interval 1 day - interval 1 second;
+-------------------------------------------------------+
| '2015-4-4 0:0:0' + interval 1 day - interval 1 second |
+-------------------------------------------------------+
| 2015-04-04 23:59:59                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

 

 

  • ADDTIME(expr1,expr2) — 扩大expr2到expr1上并重回结果;expr1
    是二个光阴恐怕日期时间表达式,expr2为二个时光表达式

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'
  • CONVERT_TZ() Convert from one timezone to another
  • CU奥迪Q5DATE() — 再次回到当前的日期 YYYY-MM-DD,

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-04-03 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+
|      20150403 |
+---------------+
1 row in set (0.00 sec)

 

  • CURRENT_DATE(), CURRENT_DATE  —- CURDATE() 的别名
  • CURRENT_TIME(), CURRENT_TIME — CURTIME()的别名
  • CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP — NOW()的别名
  • CURTIME() — 再次来到当前的年月

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:06:30  |
+-----------+
1 row in set (0.00 sec)

mysql> select curtime() + 0;
+---------------+
| curtime() + 0 |
+---------------+
| 140641.000000 |
+---------------+
1 row in set (0.00 sec)

 

  • DATE_ADD() Add time values (intervals) to a date value
  • DATE_FORMAT(date,format) 遵照格式化字符串 格式化日期

格式字符串中能够利用 ‘%’ 表达符

常用的:

  %a — 星期几的缩写  (Sun..Sat)

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

  %c — 月份 (0–12)

  %D — 天数 加上了印度语印尼语后缀 (0th,1sh…)

  %d,%e —  月份中的天数 (0-31)

  %H —  24小时制 (00- 23)

  %h,%I — 12小时制 (00-12)  

  %i — 分钟 (00-59)

  %M — 月份 (january,december)

  %m — 月份 (00-12)

  %S,%s — 秒 (00-59)

  %Y — 年 4位

  %y — 年 2位

mysql> select date_format(now(),'%Y/%m/%d %H:%m:%s');
+----------------------------------------+
| date_format(now(),'%Y/%m/%d %H:%m:%s') |
+----------------------------------------+
| 2015/04/03 14:04:05                    |
+----------------------------------------+
1 row in set (0.00 sec)

 

 

  • DATE_SUB() Subtract a time value (interval) from a date
  • DATE() — 提取日期部分

mysql> select date(now());
+-----------------+
| date(curdate()) |
+-----------------+
| 2015-04-03      |
+-----------------+
1 row in set (0.00 sec)

 

  • DATEDIFF(expr1,expr2) 再次来到expr1 –
    expr第22中学间的年华差;expr1,expr2表明式唯有日期部分加入运算

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

 

  • DAY() Synonym for DAYOFMONTH()
  • DAYNAME() Return the name of the weekday
  • DAYOFMONTH() Return the day of the month (0-31)
  • DAYOFWEEK() Return the weekday index of the argument
  • DAYOFYEAR() Return the day of the year (1-366)
  • EXTRACT() Extract part of a date
  • FROM_DAYS() Convert a day number to a date
  • FROM_UNIXTIME(unix_timestamp[,format])
    将unix时间戳格式化为日期字符串

mysql> select from_unixtime(unix_timestamp(),'%Y-%m-%d');
+--------------------------------------------+
| from_unixtime(unix_timestamp(),'%Y-%m-%d') |
+--------------------------------------------+
| 2015-04-03                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

 

  • GET_FORMAT() Return a date format string
  • HOULacrosse(time) — 提取小时有个别

mysql> select HOUR(now());
+-------------+
| HOUR(now()) |
+-------------+
|          14 |
+-------------+
1 row in set (0.00 sec)

 

  • LAST_DAY(date)–
    再次回到钦点月份的尾声一天。如若参数无效,则赶回NULL。

mysql> select last_day('2013-2-5');
+----------------------+
| last_day('2013-2-5') |
+----------------------+
| 2013-02-28           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-5');
+----------------------+
| last_day('2004-2-5') |
+----------------------+
| 2004-02-29           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-35');
+-----------------------+
| last_day('2004-2-35') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

 

  • LOCALTIME(), LOCALTIME Synonym for NOW()
  • LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
  • MAKEDATE(year,dayofyear) Create a date from the year and day of year
  • MAKETIME MAKETIME(hour,minute,second)
  • MICROSECOND(expr) — 再次来到expr表达式的纳秒数

  • MINUTE() Return the minute from the argument
  • MONTH() Return the month from the date passed
  • MONTHNAME(date) — 再次来到月份的全称

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April            |
+------------------+
1 row in set (0.00 sec)

 

  • NOW() — 重回当前的日期和时间
  • PERIOD_ADD() Add a period to a year-month
  • PERIOD_DIFF() Return the number of months between periods
  • QUARTER() Return the quarter from a date argument
  • SEC_TO_TIME() Converts seconds to ‘HH:MM:SS’ format
  • SECOND() –再次来到秒数 (0-59)
  • STR_TO_DATE() Convert a string to a date
  • SUBDATE() A synonym for DATE_SUB() when invoked with three
    arguments
  • SUBTIME() Subtract times
  • SYSDATE() Return the time at which the function executes
  • TIME_FORMAT() Format as time
  • TIME_TO_SEC() Return the argument converted to seconds
  • TIME() Extract the time portion of the expression passed
  • TIMEDIFF() Subtract time
  • TIMESTAMP() With a single argument, this function returns the date
    or datetime expression; with two arguments, the sum of the arguments
  • TIMESTAMPADD() Add an interval to a datetime expression
  • TIMESTAMPDIFF() Subtract an interval from a datetime expression
  • TO_DAYS() Return the date argument converted to days
  • TO_SECONDS() Return the date or datetime argument converted to
    seconds since Year 0
  • UNIX_TIMESTAMP() — 返回unix时间戳

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1428042298 |
+------------------+
1 row in set (0.00 sec)

 

  • UTC_DATE() Return the current UTC date
  • UTC_TIME() Return the current UTC time
  • UTC_TIMESTAMP() Return the current UTC date and time
  • WEEK() Return the week number
  • WEEKDAY() Return the weekday index
  • WEEKOFYEAR() Return the calendar week of the date (0-53)
  • YEARubicon() — 获取年份

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2015 |
+-------------+
1 row in set (0.00 sec)

 

  • YEARWEEK() Return the year and week

日期时间管理函数,mysql日期管理函数
ADDDATE(date,interval expr unit),ADDDATE(expr,days) —
增添岁月值(时区)为日期值,其为DATE_ADD()的别名…

/****模拟sqlserver中的dateadd函数******* 
$part 类型:string 
取值范围:year,month,day,hour,min,sec 
代表:要加进的日子的哪些部分 
$n 类型:数值 
代表:要加进多少,依照$part决定扩展哪些部分 
可为负数 
$datetime类型:timestamp 
表示:扩大的基数 
返回 类型:timestamp 
**************结束**************/ 
function dateadd($part,$n,$datetime){ 
$year=date(“Y”,$datetime); 
$month=date(“m”,$datetime); 
$day=date(“d”,$datetime); 
$hour=date(“H”,$datetime); 
$min=date(“i”,$datetime); 
$sec=date(“s”,$datetime); 
$part=strtolower($part); 
$ret=0; 
switch ($part) { 
case “year”: 
$year =$n; 
break; 
case “month”: 
$month =$n; 
break; 
case “day”: 
$day =$n; 
break; 
case “hour”: 
$hour =$n; 
break; 
case “min”: 
$min =$n; 
break; 
case “sec”: 
$sec =$n; 
break; 
default: 
return $ret; 
break; 
} 
$ret=mktime($hour,$min,$sec,$month,$day,$year); 
return $ret; 
} 

  /****模拟sqlserver中的dateadd函数*******
  $part 类型:string
  取值范围:year,month,day,hour,min,sec
  代表:要增加的日期的哪个部分
  $n 类型:数值
  表示:要增增加少,依照$part决定追加哪些部分
  可为负数
  $datetime类型:timestamp
  表示:扩展的基数
  返回 类型:timestamp

  **************结束**************/ 

  **************结束**************/

function dateadd($part,$n,$datetime){ 
$year=date(”Y”,$datetime); 
$month=date(”m”,$datetime); 
$day=date(”d”,$datetime); 
$hour=date(”H”,$datetime); 
$min=date(”i”,$datetime); 
$sec=date(”s”,$datetime); 
$part=strtolower($part); 
$ret=0; 
switch ($part) { 
case “year”: 
$year+=$n; 
break; 
case “month”: 
$month+=$n; 
break; 
case “day”: 
$day+=$n; 
break; 
case “hour”: 
$hour+=$n; 
break; 
case “min”: 
$min+=$n; 
break; 
case “sec”: 
ca88官网登录 ,$sec+=$n; 
break; 
default: 
return $ret; 
break; 

$ret=mktime($hour,$min,$sec,$month,$day,$year); 
return $ret; 
}  

网站地图xml地图