Date and Time Functions

Date and time functions operate on MySQL date-time data types such as DATE and DATETIME.

9.3.1. ADDTIME

date1 =ADDTIME (date2 ,time_interval )

ADDTIME adds the specified time interval to the date-time provided and returns the amended date. Time intervals are specified in the format hh:mm:ss.hh, so you can add any time interval down to one-hundredth of a second.

SET var1=NOW( ); 2005-07-21 18:56:46 SET var2=ADDTIME(NOW( ),"0:00:01.00"); 2005-07-21 18:56:47 SET var3=ADDTIME(NOW( ),"0:01:00.00"); 2005-07-21 18:57:46 SET var4=ADDTIME(NOW( ),"1:00:00.00") ; 2005-07-21 19:56:46

 

9.3.2. CONVERT_TZ

datetime1 =CONVERT_TZ (datetime2 ,fromTZ ,toTZ )

This function converts a date-time value from one time zone to another. The valid time zone values can be found in the table mysql.time_zone_name.

You may have to load the MySQL time zone tables; for instructions, see the MySQL manual section "MySQL Server Time Zone Support."

9.3.3. CURRENT_DATE

date =CURRENT_DATE ( )

CURRENT_DATE returns the current date. It does not show the time.

SET var1=CURRENT_DATE( ); 2005-07-21

 

9.3.4. CURRENT_TIME

time =CURRENT_TIME ( )

CURRENT_TIME returns the current time. It does not show the date.

SET var1=CURRENT_TIME( ); 22:12:21

 

9.3.5. CURRENT_TIMESTAMP

timestamp =CURRENT_TIMESTAMP ( )

CURRENT_TIMESTAMP returns the current date and time in the format yyyy-mm-dd hh:mm:ss.

SET var1=CURRENT_TIMESTAMP( ); 2005-07-21 22:15:02

 

9.3.6. DATE

date =DATE (datetime )

DATE returns the date part of a date-time value.

SET var1=NOW( ); 2005-07-23 12:08:52 SET var2=DATE(var1) ; 2005-07-23

 

9.3.7. DATE_ADD

date1 =DATE_ADD (date2 , INTERVAL interval_value interval_type )

DATE_ADD returns the date-time that results from adding the specified interval to the date-time provided. Possible intervals are listed in Table 9-3.

SET var1=NOW( ); 2005-07-20 22:33:21 SET var2=DATE_ADD(NOW( ), INTERVAL 7 DAY); 2005-07-27 22:33:21 SET var3=DATE_ADD(NOW( ), INTERVAL 0623 DAY_HOUR) ; 2005-08-15 21:33:21 SET var4=DATE_ADD(NOW( ), INTERVAL 06235959 DAY_SECOND) ; 2005-10-01 02:46:00 SET var5=DATE_ADD(NOW( ), INTERVAL 2 MONTH); 2005-09-20 22:33:21 SET var6=DATE_ADD(NOW( ), INTERVAL 10 YEAR); 2015-07-20 22:33:21 SET var7=DATE_ADD(NOW( ), INTERVAL 3600 SECOND); 2005-07-20 23:33:21

Table 9-3. Date-time formats for DATE_ADD and DATE_SUB

Interval name

Interval format

DAY

dd

DAY_HOUR

ddhh

DAY_MINUTE

dd hh:mm

DAY_SECOND

dd hh:mm:ss

HOUR

hh

HOUR_MINUTE

hh:mm

HOUR_SECOND

hh:mm:ss

MINUTE

mm

MINUTE_SECOND

mm:ss

MONTH

mm

SECOND

ss

YEAR

yyyy

 

9.3.8. DATE_FORMAT

string =DATE_FORMAT (datetime ,FormatCodes )

DATE_FORMAT accepts a date-time value and returns a string representation of the date in the desired format. Format codes are shown in Table 9-4.

SET var1=NOW( ); 2005-07-23 13:28:21 SET var2=DATE_FORMAT(NOW( ),"%a %d %b %y"); Sat 23 Jul 05 SET var3=DATE_FORMAT(NOW( ),"%W, %D %M %Y"); Saturday, 23rd July 2005 SET var4=DATE_FORMAT(NOW( ),"%H:%i:%s") ; 13:28:21 SET var5=DATE_FORMAT(NOW( ),"%T"); 13:28:21 SET var6=DATE_FORMAT(NOW( ),"%r"); 01:28:22 PM

Table 9-4. Format codes for DATE_FORMAT

Code

Explanation

%%

The % sign

%a

Short day of the week (Mon-Sun)

%b

Short month name (Jan-Feb)

%c

Month number (1-12)

%d

Day of the month (1-31)

%D

Day of the month with suffix (1st, 2nd, 3rd, etc.)

%e

Day of the month, numeric (1-31)

%h

12-hour clock hour of the day (1-12)

%H

24-hour clock hour of the day (00-23)

%i

Minute of the hour (00...59)

%I

12-hour clock hour of the day (1-12)

%j

Day of the year (1-365)

%k

24-hour clock hour of the day (00-23)

%l

12-hour clock hour of the day (1-12)

%m

Month of the year (1-12)

%M

Long month name (January-December)

%p

AM/PM

%r

Hour, minute, and second of the day, 12-hour format (hh:mm:ss AM|PM)

%s

Seconds within a minute (0-59)

%S

Seconds within a minute (0-59)

%T

Hour, minute, and second of the day, 24-hour format (HH:mm:ss)

%u

Week of the year (0-52) (Monday is the first day of the week)

%U

Week of the year (0-52) (Sunday is the first day of the week)

%v

Week of the year (1-53) (Monday is the first day of the week)

%V

Week of the year (1-53) (Sunday is the first day of the week)

%w

Numeric day of the week (0=Sunday, 6=Saturday)

%W

Long weekday name (Sunday, Saturday)

%y

Year, numeric, 2 digits

%Y

Year, numeric, 4 digits

 

9.3.9. DATE_SUB

date1 =DATE_SUB (date2 , INTERVAL interval_value interval_type )

DATE_SUB returns the date-time resulting from subtracting the specified interval from the date-time provided. Possible intervals are listed in Table 9-3.

Example 9-10 shows a stored procedure that determines if an employee's date of birth indicates an age of greater than 18 years. DATE_SUB is used to create a date 18 years earlier than the current date. This date is compared to the date of birth and, if it is earlier, we can conclude that the employee is less than 18 years old.

Example 9-10. Using DATE_SUB

CREATE PROCEDURE validate_age (in_dob DATE, OUT status_code INT, OUT status_message VARCHAR(30)) BEGIN IF DATE_SUB(now( ), INTERVAL 18 YEAR)

9.3.10. DATEDIFF

days =DATEDIFF (date1 ,date2 )

DATEDIFF returns the number of days between two dates. If date2 is greater than date1, then the result will be negative; otherwise, it will be positive.

Example 9-11 uses DATEDIFF to calculate the number of days that have elapsed since a bill due date, and returns appropriate status and messages if the bill is more than 30 or 90 days old.

Example 9-11. Using DATEDIFF

CREATE PROCEDURE check_billing_status (in_due_date DATE, OUT status_code INT, OUT status_message VARCHAR(30)) BEGIN DECLARE days_past_due INT; SET days_past_due=FLOOR(DATEDIFF(now( ),in_due_date)); IF days_past_due>90 THEN SET status_code=-2; SET status_message='Bill more than 90 days overdue'; ELSEIF days_past_due >30 THEN SET status_code=-1; SET status_message='Bill more than 30 days overdue'; ELSE SET status_code=0; SET status_message='OK'; END IF; END;

9.3.11. DAY

day =DAY (date )

DAY returns the day of the month (in numeric format) for the specified date.

SET var1=NOW( ); 2005-07-23 13:47:13 SET var2=DAY(NOW( )); 23

 

9.3.12. DAYNAME

day =DAYNAME (date )

DAYNAME returns the day of the weekas in Sunday, Monday, etc.for the specified date.

SET var1=NOW( ); 2005-07-23 13:50:02 SET var2=DAYNAME(NOW( )); Saturday

 

9.3.13. DAYOFWEEK

day =DAYOFWEEK (date )

DAYOFWEEK returns the day of the week as a number, where 1 returns Sunday.

SET var1=NOW( ); 2005-07-23 13:53:07 SET var2=DATE_FORMAT(NOW( ),"%W, %D %M %Y"); Saturday, 23rd July 2005 SET var3=DAYOFWEEK(NOW( )); 7

 

9.3.14. DAYOFYEAR

day =DAYOFYEAR (date )

DAYOFYEAR returns the day of the year as a number, where 1-JAN returns 1 and 31-DEC returns 365 (except in leap years, where it returns 366).

SET var1=NOW( ); 2005-07-23 13:55:57 SET var2=DAYOFYEAR(NOW( )); 204

 

9.3.15. EXTRACT

date_part =EXTRACT (interval_name FROM date )

EXTRACT returns a specified portion of a date-time. The applicable intervals are shown in Table 9-3.

SET var1=NOW( ); 2005-07-23 14:01:03 SET var2=EXTRACT(HOUR FROM NOW( )); 14 SET var3=EXTRACT(YEAR FROM NOW( )); 2005 SET var4=EXTRACT(MONTH FROM NOW( )); 7 SET var5=EXTRACT(HOUR_SECOND FROM NOW( )); 140103 SET var6=EXTRACT(DAY_MINUTE FROM NOW( )); 231401

 

9.3.16. GET_FORMAT

format =GET_FORMAT (datetime_type ,locale )

GET_FORMAT returns a set of date formatting codesuitable for use with DATE_FORMATfor various date-time types and locales.

Format type can be one of the following:

Format code can be one of the following:

SET var1=GET_FORMAT(DATE,"USA"); %m.%d.%Y SET var2=GET_FORMAT(DATE,"ISO"); %Y-%m-%d SET var3=GET_FORMAT(DATETIME,"JIS") ; %Y-%m-%d %H:%i:%s SET var4=NOW( ); 2005-07-24 13:27:58 SET var5=DATE_FORMAT(NOW( ),GET_FORMAT(DATE,"USA")); 07.24.2005

 

9.3.17. MAKEDATE

date =MAKEDATE (year ,day )

MAKEDATE takes the year (YYYY) and day-of-year arguments and converts them to a date value. The day-of-year argument is in the form that would be returned by DAYOFYEAR.

SET var1=MAKEDATE(2006,1); 2006-01-01 SET var2=MAKEDATE(2006,365); 2006-12-31 SET var3=MAKEDATE(2006,200); 2006-07-19

 

9.3.18. MAKETIME

time =MAKETIME (hour ,minute ,second )

MAKETIME takes the hour, minute, and second arguments and returns a time value.

SET var4=MAKETIME(16,30,25); 16:30:25 SET var5=MAKETIME(0,0,0); 00:00:00 SET var6=MAKETIME(23,59,59); 23:59:59

 

9.3.19. MONTHNAME

monthname =MONTHNAME (date )

MONTHNAME returns the full name of the month corresponding to the provided date.

SET var1=NOW( ); 2005-07-24 13:44:54 SET var2=MONTHNAME(NOW( )); July

 

9.3.20. NOW

datetime =NOW ( )

NOW returns the current date and time. We have used this function in many previous examples as input to date and time functions.

9.3.21. SEC_TO_TIME

time =SEC_TO_TIME (seconds )

SEC_TO_TIME returns a time value for a given number of seconds. The time is shown in hours, minutes, and seconds.

SET var1=SEC_TO_TIME(1); 00:00:01 SET var2=SEC_TO_TIME(3600); 01:00:00 SET var3=SEC_TO_TIME(10*60*60); 10:00:00

 

9.3.22. STR_TO_DATE

date =STR_TO_DATE (string ,format )

STR_TO_DATE takes a string representation of a date (as might be returned by DATE_FORMAT) and returns a standard date data type in the format specified by the format argument. The format string is the same as that used in DATE_FORMAT; possible values are listed in Table 9-4.

SET var1=STR_TO_DATE("Sun 24 Jul 05","%a %d %b %y"); 2005-07-24 SET var2=STR_TO_DATE("Sunday, 24th July 2005","%W, %D %M %Y"); 2005-07-24 SET var3=STR_TO_DATE("3:53:54","%H:%i:%s"); 03:53:54 SET var4=STR_TO_DATE("13:53:54","%T"); 13:53:54 SET var5=STR_TO_DATE("01:53:54 PM","%r"); 13:53:54

 

9.3.23. TIME_TO_SEC

seconds =TIME_TO_SEC (time )

TIME_TO_SEC returns the number of seconds in the specified time value. If a date-time is provided, TIME_TO_SEC provides the number of seconds in the time part of that date only.

SET var1=NOW( ); 2005-07-24 14:05:21 SET var2=TIME_TO_SEC("00:01:01"); 61 SET var3=TIME_TO_SEC(NOW( )); 50721

 

9.3.24. TIMEDIFF

time =TIMEDIFF (datetime1 ,datetime2 )

TIMEDIFF returns the time difference between two arguments specified as date-time data types.

SET var1=TIMEDIFF("2005-12-31 00:00:01","2005-12-31 23:59:59"); -23:59:58

 

9.3.25. TIMESTAMP

datetime =TIMESTAMP (date ,time )

TIMESTAMP returns a date-time value from a specified date and time.

SET var2=TIMESTAMP("2005-12-31","23:30:01"); 2005-12-31 23:30:01

 

9.3.26. TIMESTAMPADD

date_time =TIMESTAMPADD (interval_type ,interval_value ,date_time )

TIMESTAMPADD adds the specified interval_value, which is of the interval_type data type, to the datetime provided and returns the resulting date-time.

Possible values for interval_type are listed in Table 9-3.

SET var1=NOW( ); 2005-07-31 16:08:18 SET var2=TIMESTAMPADD(YEAR,100,NOW( )); 2105-07-31 16:08:18 SET var3=TIMESTAMPADD(HOUR,24,NOW( )); 2005-08-01 16:08:18

 

9.3.27. TIMESTAMPDIFF

interval_value =TIMESTAMPDIFF (interval_type ,date_time1 ,date_time2 )

TIMESTAMPDIFF returns the difference between two date-times, expressed in terms of the specified interval_type.

SET var1=NOW( ); 2005-07-31 16:12:30 SET var2=TIMESTAMPDIFF(YEAR,NOW( ),"2006-07-31 18:00:00"); 1 SET var3=TIMESTAMPDIFF(HOUR,NOW( ),"2005-08-01 13:00:00"); 20

 

9.3.28. WEEK

number =WEEK (date_time [,start_of_week ])

WEEK returns the number of weeks since the start of the current year. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

SET var1=NOW( ); 2005-07-31 16:20:09 SET var2=WEEK(NOW( )); 31

 

9.3.29. WEEKDAY

number =WEEKDAY (date )

WEEKDAY returns the number for the current day of the week, with Monday returning a value of 0.

SET var1=NOW( ); 2005-07-31 16:22:05 SET var2=DAYNAME(NOW( )); Sunday SET var3=WEEKDAY(NOW( )); 6

 

9.3.30. YEAR

number =YEAR (datetime )

YEAR returns the year portion of the datetime argument, which is specified in date-time format.

SET var1=NOW( ); 2005-07-31 16:27:12 SET var2=YEAR(NOW( )); 2005

 

9.3.31. YEARWEEK

YearAndWeek =YEARWEEK (datetime [,StartOfWeek ])

YEARWEEK returns the year and week of the year for the given date. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

SET var1=NOW( ); 2005-07-31 16:30:24 SET var2=DAYNAME(NOW( )); Sunday SET var3=YEARWEEK(NOW( )); 200531 SET var4=YEARWEEK(NOW( ),1); 200530

 

9.3.32. Other Date and Time Functions

Table 9-5 lists date and time functions not discussed in previous sections. Some of these are synonyms for functions we have discussed above, while others are rarely required in MySQL programming.

Table 9-5. Additional date-time functions

Function

Syntax

Description

ADDDATE

datetime=ADDDATE(date,interval_value, intervaltype)

Synonym for DATE_ADD.

CURDATE

datetime=CURDATE( )

Alias for NOW.

CURTIME

time=CURTIME( )

Current time.

DAYOFMONTH

day=DAYOFMONTH(datetime)

Day of the month.

FROM_DAYS

days=FROM_DAYS(datetime)

Number of days since the start of the current calendar.

HOUR

number=HOUR(datetime)

Hour of the day for the given date.

LAST_DAY

date=LAST_DAY(date)

Returns the last day of the month for the given date.

LOCALTIME

datetime=LOCALTIME( )

Synonym for NOW.

LOCALTIMESTAMP

datetime=LOCALTIMESTAMP( )

Synonym for NOW.

MICROSECOND

microseconds=MICROSECOND(datetime)

Microsecond portion of the provided time.

MINUTE

minute=MINUTE(datetime)

Minute part of the given time.

MONTH

month=MONTH(datetime)

Month part of the given time.

PERIOD_ADD

date=PERIOD_ADD(year_month, months)

Adds the specified number of months to the provided year_month value.

PERIOD_DIFF

date=PERIOD_DIFF( year_month_1,year_month_2)

Returns the number of months between the two year_month values provided.

QUARTER

quarter=QUARTER(datetime)

Returns the quarter of the given date.

SECOND

seconds=SECOND(datetime)

Returns the seconds portion of the provided datetime.

SUBDATE

date1=SUBDATE(date2, interval_value, interval_type)

Synonym for DATE_SUB.

SUBTIME

datetime1=SUBTIME(datetime2, time)

Subtracts the time from the datetime.

SYSDATE

datetime=SYSDATE( )

Synonym for NOW.

TO_DAYS

datetime=TO_DAYS(days)

Adds the days argument to the start of the standard calendar.

WEEKOFYEAR

week=WEEKOFYEAR(datetime)

Synonym for WEEK.

Related

Категории