Implementation of time comparison in MySQL

The   unix_timestamp function can accept a parameter, or it can not use a parameter. Its return value is an unsigned integer. Without parameters, it returns the number of seconds since January 1, 1970 0:0 0 seconds to now, and if parameters are used, the type of the parameter is a string representation of a time type or a time type, which is the number of seconds elapsed from 1970-01-01 00:00:00 to a specified time. With this function, it is natural to convert the time comparison to an unsigned integer comparison.    For example, determine whether a time is within a range of   unix_timestamp between Unix_timestamp (' start ') and Unix_timestamp ('　End ')    for a description of the range of values owned by each type and the valid format of the specified date and time value, see 7.3.6 Date and Time type.    Here is an example of using a date function. The following query selects all records whose Date_col value is within the last 30 days:   mysql> Select something from table  where to_days (now ())-to_　Days (Date_col) <= 30;   dayofweek (date)   Returns the day of the Week index (1= Sunday, 2 = Monday, ... 7= Saturday).　These index values correspond to the ODBC standard.　 mysql> Select DAYOFWEEK (' 1998-02-03 ');  -> 3   weekday (date)   Returns the day of the Week index (0= Monday, 1 = Tuesday, ...).　6= Sunday).　 mysql> Select WEEKDAY (' 1997-10-04 22:23:00 ');　 -> 5  mysql> Select WEEKDAY (' 1997-11-05 ');　 -> 2   dayofmonth (date)   Returns the month of date in the range 1 to 31.  mysql> Select DayOfMonth (' 1998-02-03 ');　 -> 3   dayofyear (date)   Returns the number of days in a year, in the range 1 to 366.　 mysql> Select DayOfYear (' 1998-02-03 ');　 ->   month (date)   Returns the month of date, ranging from 1 to 12.　 mysql> Select MONTH (' 1998-02-03 ');　 -> 2   dayname (date)   Returns the week name of date.　 mysql> Select Dayname ("1998-02-05");　 -> ' Thursday '   monthname (date)   Returns the month name of date.　 mysql> Select MONTHNAME ("1998-02-05");　 -> ' February '  www.2cto.com     quarter (date)   Returns the quarter of a date year, ranging from 1 to 4.　 mysql> Select QUARTER (' 98-04-01 ');  -> 2   week (date)   week (Date,first)   for Sunday is the first day of the week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. 2 Parameter Form week () allows   you to specify whether the week starts in Sunday or Monday.　If the second parameter is 0, the week starts from Sunday, if the second parameter is 1,   starts from Monday.　 mysql> Select WEEK (' 1998-02-20 ');　 -> 7  mysql> Select WEEK (' 1998-02-20 ', 0);　 -> 7  mysql> Select WEEK (' 1998-02-20 ', 1);  -> 8   year (date)   returnThe year of the date returned, ranging from 1000 to 9999.　 mysql> Select year (' 98-02-03 ');　 -> 1998   hour (time)   Returns the hour of time, ranging from 0 to 23.　 mysql> Select HOUR (' 10:05:03 ');　 ->   minute (Time)   Returns the minute of time, ranging from 0 to 59.　 mysql> Select MINUTE (' 98-02-03 10:05:03 ');　 -> 5   second (time)   The number of seconds to return time, ranging from 0 to 59.　 mysql> Select SECOND (' 10:05:03 ');  -> 3   period_add (p,n)   added N months to stage P (in format yymm or yyyymm). Returns a value in the format yyyymm.　Note that the stage parameter p is not a date value.　 mysql> Select Period_add (9801,2);  -> 199803   period_diff (P1,P2)   Returns the number of months between P1 and P2 in the period, P1 and P2 should be in the format yymm or yyyymm.　Note that the time parameters P1 and P2 are not date values.　 mysql> Select Period_diff (9802,199703);  ->   date_add (date,interval expr type)   date_sub (Date,interval expr type)    adddate (Date,interval expr type)   subdate (Date,interval expr type)   Perform date operations on these functions. For MySQL 3.22, they are new. Adddate () and subdate () are synonyms of date_add () and Date_sub ().   in MySQL 3.23, you canTo use + and-instead of Date_add () and Date_sub (). (see example) date is a  datetime or date value that specifies the start date, and expr is an expression that specifies the interval value to be subtracted from the start date or from the start date, and expr is a string; it can start with a   "-" to represent a negative interval. Type is a keyword that indicates how an expression should be interpreted. The EXTRACT (type from date) function returns the "type" interval from date  . The following table shows how the type and expr parameters are associated: type value meaning expected expr format   second sec SECONDS  minute min MINUTES  hour time HOURS  d AY Day days  month month MONTHS  year year years  minute_second minutes and seconds "minutes:seconds"  hour_minute hours and minutes "HOUR S:minutes " day_hour Day and Hour" Days HOURS " year_month year and Month" Years-months " hour_second hours, Minutes," hours:minutes: SECONDS " day_minute Day, hour, minute" Days Hours:minutes " day_second day, hour, minute, seconds" Days HOURS:MINUTES:SECONDS "   mysql allows any punctuation delimiter in the expr format. Indicates that the suggested delimiter is displayed. If the date parameter is a date value and your calculation only   contains the year, month, and day portions (that is, there is no time part), the result is a date value.　Otherwise, the result is a datetime value.　  mysql> Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  -> 1998-01-01 00:00:00  www.2cto.com   mysql> Select INTERVAL 1 day + "1997-12-31 ";　 -> 1998-01-01  mysql> Select "1998-01-01"-INTERVAL 1 SECOND;　 -> 1997-12-31 23:59:59  mysql> Select Date_add ("1997-12-31 23:59:59",  interval 1 SECOND);　 -> 1998-01-01 00:00:00  mysql> Select Date_add ("1997-12-31 23:59:59",  interval 1 day);  -> 1998-01-01 23:59:59  mysql> Select Date_add ("1997-12-31 23:59:59",  interval "1:1" minute_　SECOND);  -> 1998-01-01 00:01:00  mysql> Select Date_sub ("1998-01-01 00:00:00",  interval "1 1:1:1" Day_　SECOND);　 -> 1997-12-30 22:58:59  mysql> Select Date_add ("1998-01-01 00:00:00",  interval "-1" day_hour);　 -> 1997-12-30 14:00:00  mysql> Select Date_sub ("1998-01-02", INTERVAL Day);　 -> 1997-12-02  mysql> Select EXTRACT (Year from "1999-07-02");　 -> 1999  mysql> Select EXTRACT (year_month from "1999-07-02 01:02:03");  -> 199907  mysql> SELECT EXTRACT (Day_minute from "1999-07-02 01:02:03");  -> 20102    If you specify an interval value that is too short (excluding the desired interval portion of the type keyword), MySQL assumes that you omitted the leftmost part of the interval value. For example,   if you specify a type of Day_second, the value expr is expected to have the day, hour, minute, and second parts. If you specify a value like "1:10",  mysql assumes that the day and hour portions are missing and the values represent minutes and seconds. In other words, the "1:10" Day_second is interpreted in the same way that it is equivalent to "1:10" Minute_second  , which explains that MySQL interprets the time value as having two semantics in the way elapsed rather than as the time of day. If you use a date that is really incorrect, the   result is null.　If you increase month, year_month, or year and the result date is greater than the maximum number of days for the new month, the days are adjusted with the largest day in the crescent.　  mysql> Select Date_add (' 1998-01-30 ', Interval 1 month);　 -> 1998-02-28    Note that from the previous example morphemes interval and type keywords are not case-sensitive.　 to_days (date)   gives a date, which returns a number of days (from 0 years).　 mysql> Select To_days (950501);　 -> 728779  mysql> Select To_days (' 1997-10-07 ');　 -> 729669   to_days () is not intended for use with Gregory (1582) before the value appears.　  from_days (n)   gives a number of days N, which returns a date value.　 mysql> Select From_days (729669);　 -> ' 1997-10-07 '   to_days () is not intended for use with Gregory (1582) before the value appears.   date_format (Date,format)   formatting a DATE based on the format stringValue. The following modifiers can be used in the format string:%M month name (January ... December)  www.2cto.com   %w week name (Sunday ... Saturday)  %d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )  %y year, number, 4-digit  %y year, number, 2-bit  %a abbreviated weekday name (Sun ... Sat)  %d number of days in the month, number (00 ...  %e number of days in the month, number (0 ...  %m months, numbers (01 ...  %c month, number (1 ...)  %b abbreviated month name (Jan ... DEC)  %j Days of the year (001 ... 366)  %h hours (00 ...  %k hours (0 ...)  %h hours (01 ...)  %i) Hours (01 ...  %l hours (1 ...)  %i minutes, Numbers (00 ...)  %r time, 12 hours (Hh:mm:ss [ap]m)  %t time, 24 hours (HH:MM:SS)  %s seconds (00 ...  %s seconds (00 ...)  %p am or PM  %w one days of the week (0=sunday ... 6=saturday)  %u Week (0 ... 52), here Sunday is the first day of the Week  %u week (0 ...　52), here Monday is the first day of the week  %% a writing "%".　   all other characters do not interpret are copied into the results.　  mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');　 -> ' Saturday October 1997 '  mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');  -> ' 22:23:00 '  mysql> select Date_fORMAT (' 1997-10-04 22:23:00 ',   '%d%y%a%d%m%b%j ');  -> ' 4th Sat, Oct 277 '  mysql> Select Date_format (' 1997-10-04 22:23:00 ',   '%H%k%I%r%T%s　%w ');  -> ' 10:23:00 PM 22:23:00 6 '  mysql3.23,% is required before the format modifier character.　In the earlier version of MySQL,% is optional.   time_format (Time,format)   This is used like the Date_format () function above, but the format string can contain only those formatting modifiers that handle hours, minutes, and seconds.　  Other modifiers produce a null value or 0.　 curdate ()   current_date   Returns today's date value in ' Yyyy-mm-dd ' or YYYYMMDD format, depending on whether the function is used in a string or a numeric context.　 mysql> select Curdate ();　 -> ' 1997-12-15 '  mysql> select curdate () + 0;  -> 19971215   curtime ()   current_time   Returns the current time value in the ' HH:MM:SS ' or HHMMSS format,　Depends on whether the function is used in a string or in the context of a number.　 mysql> select Curtime ();　 -> ' 23:50:26 '  mysql> select curtime () + 0;  -> 235026   now ()   sysdate ()   current_timestamp   with ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format returns the current date and time, depending on whether the function is in a string or a number   www.2cto.com   context is used.　 mysql> Select Now ();　 -> ' 1997-12-15 23:50:26 '  mysql> Select now () + 0;  -> 19971215235026   unix_timestamp ()   unix_timestamp (date)   If no argument is called, Returns a UNIX timestamp (the number of seconds starting from ' 1970-01-01 00:00:00 ' GMT). If Unix_timestamp () is called with a   date parameter, it returns the value of the second starting at ' 1970-01-01 00:00:00 ' GMT.　Date can be a date string, a datetime   string, a timestamp, or a number in the local time in YYMMDD or YYYYMMDD format.　 mysql> select Unix_timestamp ();　 -> 882226357  mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 ');　 -> 875996580    When Unix_timestamp is used for a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transform.   from_unixtime (Unix_timestamp)   Returns the value represented by the YYYYMMDDHHMMSS parameter in ' Yyyy-mm-dd HH:MM:SS ' or unix_timestamp format,　Depends on whether the function is used in a string   or in a numeric context.　 mysql> Select From_unixtime (875996580);　 -> ' 1997-10-04 22:23:00 '  mysql> select From_unixtime (875996580) + 0;  -> 19971004222300  www.2cto.coM    from_unixtime (Unix_timestamp,format)   Returns a String representing the Unix time tag, formatted according to the format string.　FORMAT can contain the same modifiers as those listed in the Date_format () function  .　 mysql> Select From_unixtime (Unix_timestamp (),   '%Y%d%M%h:%i:%s%x ');  -> ' 1997 23rd December 03:43:30 X '   sec_to_time (seconds)   return seconds parameter, change to hours, minutes and seconds, value ' HH:MM:SS　' or HHMMSS formatted, depending on whether the function is used in a string or in a number   context.　 mysql> Select Sec_to_time (2378);　 -> ' 00:39:38 '  mysql> select Sec_to_time (2378) + 0;　 -> 3938   time_to_sec (time)   Returns the time parameter, converted to seconds.　 mysql> Select Time_to_sec (' 22:23:00 ');　 -> 80580  mysql> Select time_to_sec (' 00:39:38 ');  -> 2378   

Implementation of time comparison in MySQL