Oracle and Mysql time formatting

Oracle and Mysql time formatting 1. Oracle formatting time: Oracle obtains the current date and Date Format

Get system date: SYSDATE () format Date: TO_CHAR (SYSDATE (), 'yy/MM/DD HH24: MI: SS) or TO_DATE (SYSDATE (), 'yy/MM/DD HH24: MI: SS) format the number: TO_NUMBER Note: TO_CHAR converts the date or number to the string TO_CHAR (number, 'format') TO_CHAR (salary, '$99,999.99') TO_CHAR (date, 'format') TO_DATE converts the string to the date type TO_DATE (char, 'format') TO_NUMBER in the database and converts the string to the number TO_NUMBER (char, 'format') returns the system date. The output value is 25-12-09 select sysdate from dual; the value of mi is minute, and the output value is 2009-12-25 14: 23: 31 select to_char (sysdate, 'Yyyy-MM-dd HH24: mi: ss') from dual; mm displays the month, and outputs 2009-12-25 14:12:31 select to_char (sysdate, 'yyyy-MM-dd HH24: mm: ss ') from dual; Output 09-12-25 14: 23: 31 select to_char (sysdate, 'yy-mm-dd hh24: mi: ss ') from dual Output 2009-12-25 14: 23: 31 select to_date ('2017-12-25 14:23:31 ', 'yyyy-mm-dd, hh24: mi: ss ') from dual, if you write the above: select to_date ('2017-12-25 14:23:31 ', 'yyyy-mm-dd, hh: mi: ss ') from dual, an error is reported because the hour hh is in 12 hexadecimal format and 14 is invalid. Match. Output $10,000, 00: select to_char (1000000, '$99,999, 99') from dual; Output RMB10, 000,00: select to_char (1000000, 'l99, 999,99') from dual; output 1000000.12: select trunc (to_number ('123. 123 '), 2) from dual; select to_number ('2017. 123 ') from dual; format of conversion: year: y indicates the last digit of the Year, yy indicates the last two digits of the Year, yyy indicates the last three digits of the Year, and yyyy indicates the month in four digits: mm uses two digits to represent the month and mon in short form. For example, January 1, November, nov, and month use the full name. For example, January 1, November or November represents day: dd represents the day of the month, d Dd indicates the day of the current year and the day of the dy week. Short for it, for example, the day of the Friday, fri, and day of the week. For example, Friday or Friday indicates hour: hh 2-digit represents 12-digit hour, hh24 2-digit represents 24-hour represents minute: mi 2-digit represents minute represents second: ss 2-digit indicates the second 60-digit indicates the quarter: q 1-digit indicates the quarter (1-4) and ww indicates the week w of the current month. Time range in the 24-hour format: 00: 00: 00-23: 59: 5912 hour format: 1: 00: 00-12:59:59 digit format: 9 indicates that a number 0 is forcibly displayed. 0 $ is placed with a $ character L is placed with a floating local currency character. show the decimal point, and display the thousands indicator. Supplement: select sysdate, sysdate-interval '7' MINUTE from dual in the current time minus 7 minutes; current Time minus 7 hours select sysdate-interval '7' hour from dual; current time minus 7 days select sysdate-interval '7' day from dual; select sysdate, sysdate-interval '7' month from dual; current time minus 7 years select sysdate, Sysdate-interval '7' year from dual; Time interval multiplied by a number select sysdate, sysdate-8 * interval '7' hour from dual; meaning: the Dual pseudo-column Dual is an existing table in Oracle and can be read by any user. It is often used in select statement blocks without a target table. Different systems may return different date formats. Returns the user of the current connection: select user from dual; 2. mysql format time: MYSQL obtains the current date and date format. Obtain the system date: NOW () format date: DATE_FORMAT (date, format) note: date: Time Field format: return the system date in date format, output 2009-12-25 14: 38: 59 select now (); Output 09-12-25select date_format (now (), '% y-% m-% D'); format the date value based on the format string in the format of % S, % s ,..., 59) % I, % I two-digit score ,..., 59) % H two-digit hour, 24 hours ,..., 23) % h two-digit hour, 12 hours ,..., 12) % k number in the form of hour, 24 hours (0, 1 ,..., 2 3) % l number of hours, 12 hours (1, 2 ,..., 12) % T 24-hour time format (hh: mm: ss) % r 12-hour time format (hh: mm: ss AM or hh: mm: ss PM) % p AM or PM % W name of each day in a week (Sunday, Monday ,..., saturday) % a abbreviation of the name of each day in a week (Sun, Mon ,..., sat) % d indicates the number of days in a month (00, 01 ,..., 31) % e indicates the number of days in the month (1, 2 ,..., 31) % D indicates the number of days in a month (1st, 2nd, 3rd ,...) % w represents the number of days in a week (0 = Sunday, 1 = Monday ,..., 6 = Saturday) % j represents the number of days in the year (001,002 ,..., 366) % U Week (0, 1, 52), where Sunday is the first day of the week % u Week (0, 1, 52), where Monday is the first day of the week % M name (January, February ,..., december) % B abbreviated month name (January, February ,...., december) % m double-digit month (01, 02 ,..., 12) % c indicates the month (1, 2 ,...., 12) % Y four-digit year % y two-digit year % direct value "%" curdate () mySQL returns the current date and time function 1.1 returns the current date + time (date + time) function: now () mysql> select now (); + ------------------- + | now () | + --------------------- + | 22:20:46 | + ----------------------- + in addition to the now () function YSQL also has the following functions: current_timestamp (), current_timestamp, localtime (), localtime, localtimestamp -- (v4.0.6), localtimestamp () -- (v4.0.6, is equivalent to now (). Considering that the now () function is short and easy to remember, we recommend that you always use now () to replace the functions listed above. 1.2 obtain the current date + time (date + time) function: The sysdate () date and time function is similar to the now () function. The difference is that: now () the execution start value is obtained, and sysdate () dynamically obtains the value during function execution. See the following example to understand: mysql> select now (), sleep (3), now (); + --------------------- + ---------- + --------------------- + | now () | sleep (3) | now () | + hour + ---------- + ------------------- + | 22:28:21 | 0 | 22:28:21 | + hour + ---------- + --------------------- + mysql> select sysdate (), sleep (3 ), sysdate (); + --------------------- + ---------- + --------------------- + | sysda Te () | sleep (3) | sysdate () | + --------------------- + ---------- + hour + | 22:28:41 | 0 | 22:28:44 | + --------------------- + ---------- + hour +, although sleep lasts for 3 seconds, the time value of the now () function is the same. The time value obtained by the sysdate () function is 3 seconds different. In MySQL Manual, sysdate () is described as follows: Return the time at which the functionexecutes. Sysdate () Date and time functions are rarely used. 2. obtain the current date function: curdate () mysql> select curdate (); + ------------ + | curdate () | + ------------ + | 2008-08-08 | + ------------ + where, the following two date functions are equivalent to curdate (): current_date (), current_date3. obtain the current time function: curtime () mysql> select curtime (); + ----------- + | curtime () | + ----------- + | 22:41:30 | + ----------- + where, the following two time functions are equivalent to curtime (): current_time (), current_time4. obtain the current UTC date and time functions: utc_date (), utc_time (), utc_timestamp () my SQL> select utc_timestamp (), utc_date (), utc_time (), now () + hour + -------------- + ------------ + ------------------- + | utc_timestamp () | utc_date () | utc_time () | now () | + pipeline + ------------ + ------------------- + | 14:47:11 | 14:47:11 | + pipeline + ------------ + pipeline + because our country is located in the east So the local time = UTC time + 8 hours. UTC time is very useful when your business involves multiple countries and regions.

 

Ii. MySQL Date and Time Extract (select) function.

1. select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond set @ dt = '2017-09-10 07:15:30. 123456 '; select date (@ dt); -- 2008-09-10select time (@ dt); -- 07:15:30. 123456 select year (@ dt); -- 2008 select quarter (@ dt); -- 3 select month (@ dt); -- 9 select week (@ dt ); -- 36 select day (@ dt); -- 10 select hour (@ dt); -- 7 select minute (@ dt); -- 15 select second (@ dt ); -- 30 select microsecond (@ dt); -- 1234562. mySQL Extract () function, which can implement similar Function: set @ dt = '2017-09-10 07:15:30. 123456 '; select extract (year from @ dt); -- 2008 select extract (quarter from @ dt); -- 3 select extract (month from @ dt ); -- 9 select extract (week from @ dt); -- 36 select extract (day from @ dt); -- 10 select extract (hour from @ dt ); -- 7 select extract (minute from @ dt); -- 15 select extract (second from @ dt); -- 30 select extract (microsecond from @ dt); -- 123456 select extract (ye Ar_month from @ dt); -- 200809 select extract (day_hour from @ dt); -- 1007 select extract (day_minute from @ dt); -- 100715 select extract (day_second from @ dt ); -- 10071530 select extract (day_microsecond from @ dt); -- 10071530123456 select extract (hour_minute from @ dt); -- 715 select extract (hour_second from @ dt ); -- 71530 select extract (hour_microsecond from @ dt); -- 71530123456 select extract (minute_second From @ dt); -- 1530 select extract (minute_microsecond from @ dt); -- 1530123456 select extract (second_microsecond from @ dt); -- 30123456 MySQLExtract () function except date (), in addition to the time () function, other functions must be complete. You can also select 'day _ microsecond. Note that we will not only select day and microsecond, but choose from day of the date to microsecond. Powerful enough! The only bad thing about the MySQL Extract () function is that you need to press the keyboard several more times. 3. MySQL dayof... Functions: dayofweek (), dayofmonth (), and dayofyear () return the date parameters in the positions of one week, one month, and one year. Set @ dt = '2014-08-08 '; select dayofweek (@ dt); -- 6 select dayofmonth (@ dt); -- 8 select dayofyear (@ dt ); -- The 221 date '2017-08-08 'is the first day of the week (1 = Sunday, 2 = Monday ,..., 7 = Saturday); January days in 8th; 221st days in a year. 4. MySQL week... Function: week (), weekofyear (), dayofweek (), weekday (), yearweek () set @ dt = '2017-08-08 '; select week (@ dt ); -- 31 select week (@ dt, 3); -- 32 select weekofyear (@ dt); -- 32 select dayofweek (@ dt); -- 6 select weekday (@ dt ); -- 4 select yearweek (@ dt); -- 200831 MySQL week () function, which can have two parameters. For details, see the manual. Similar to weekofyear () and week (), weekofyear is the day of the year in which the calculation "one day" is located. Weekofyear (@ dt) is equivalent to week (@ dt, 3 ). The MySQLweekday () function is similar to dayofweek () and returns the position of "one day" in a week. The difference lies in the reference standard, weekday :( 0 = Monday, 1 = Tuesday ,..., 6 = Sunday); dayofweek :( 1 = Sunday, 2 = Monday ,..., 7 = Saturday) MySQL yearweek () function, returns year (2008) + week position (31 ). 5. mySQL returns the week and month name functions: dayname (), monthname () set @ dt = '2017-08-08 '; select dayname (@ dt); -- Fridayselect monthname (@ dt ); -- August: How can I return a Chinese name? 6. MySQL last_day () function: returns the last day of the month. Select last_day ('2017-02-01 '); -- 2008-02-29select last_day ('2017-08-08'); -- The 2008-08-31MySQL last_day () function is very useful, for example, how many days do I want to get in the current month, it can be calculated as follows: mysql> select now (), day (last_day (now () as days; + ------------------- + ------ + | now () | days | + --------------------- + ------ + | 11:45:45 | 31 | + ----------------------- + ------ +

Iii. MySQL Date and Time Calculation Functions

1. mySQL adds a time interval for the date: date_add () set @ dt = now (); select date_add (@ dt, interval 1 day); -- add 1 dayselect date_add (@ dt, interval 1 hour); -- add 1 hourselect date_add (@ dt, interval 1 minute );--... select date_add (@ dt, interval 1 second); select date_add (@ dt, interval 1 microsecond); select date_add (@ dt, interval 1 week); select date_add (@ dt, interval 1 month); select date_add (@ dt, interval 1 quarter); se Lect date_add (@ dt, interval 1 year); select date_add (@ dt, interval-1 day); -- sub 1 dayMySQL adddate (), addtime () function, it can be replaced by date_add. The following is an example of addtime () implemented by date_add (): mysql> set @ dt = '2017-08-09 12:12:33 '; mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second); + ------------------------------------------------ + | date_add (@ dt, interval '01: 15: 30' hour_second) | + ---------------------------------------------- + | 13:28:03 | + ------------------------------------------------------ + mysql> select date_add (@ dt, interval '1 01:15:30 'day_second); + hour + | date_add (@ dt, interval '1 01:15:30 'day_second) | + hour + | 13:28:03 | + hour + date_add () function, added "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" for @ dt respectively ". Suggestion: always use the date_add () Date and Time Function to replace adddate () and addtime (). 2. mySQL is the date minus a time interval: date_sub () mysql> select date_sub ('2017-01-01 00:00:00 ', interval '1' day_second ); + metric + | date_sub ('2017-01-01 00:00:00 ', interval '1 22:58:59' day_second) | + metric + | 1998 | + metric + MySQL The date_sub () Date and time functions are used in the same way as date_add. In addition, there are two subdate () and subtime () functions in MySQL. We recommend that you use date_sub () instead. 3. mySQL alternative date functions: period_add (P, N), period_diff (P1, P2) function parameter "P" format is "YYYYMM" or "YYMM ", the second parameter "N" indicates adding or subtracting N month ). MySQL period_add (P, N): date plus/minus N months. Mysql> select period_add (20080808), period_add (20080808,-2) + ------------------------ + --------------------------- + | period_add (, 2) | period_add (,-2) | + -------------------- + bytes + | 200810 | 20080806 | + ---------------------------- + ----------------------- + MySQL period_diff (P1, P2): Date P1-P2, returns N months. Mysql> select period_diff (200808,200 801); + --------------------------- + | period_diff (200808,200 801) | + period + | 7 | + period + in MySQL, the two date functions, it is rarely used in general cases. 4. MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2) MySQL datediff (date1, date2): two dates subtract date1-date2 and return days. Select datediff ('2017-08-08 ', '2017-08-01'); -- 7 select datediff ('2017-08-01 ', '2017-08-08 '); ---7 MySQL timediff (time1, time2): two dates minus time1-time2, return the time difference. Select timediff ('2017-08-08 08:08:08 ', '2017-08-08 00:00:00'); -- 08: 08: 08 select timediff ('08: 08: 08 ', '00: 00: 00'); -- 08:08:08 Note: The two parameter types of the timediff (time1, time2) function must be the same.

 

Iv. MySQL date conversion function and time Conversion Function

1. mySQL (time, second) Conversion Function: time_to_sec (time), sec_to_time (seconds) select time_to_sec ('01: 00: 05 '); -- 3605 select sec_to_time (3605 ); -- '01: 00: 05 '2. mySQL (date, number of days) Conversion Function: to_days (date), from_days (days) select to_days ('2017-00-00 '); -- 0 select to_days ('2017-08-08 '); -- 733627 select from_days (0); -- '2017-00-00 'select from_days (0000); -- '2017-08-08' 3. mySQL Str to Date (string converted to Date) function: str_to_date (str, format) selec T str_to_date ('2014/1/123', '% m/% d/% y'); -- 2008-08-09select str_to_date ('2014/1/08 ', '% m/% d/% y'); -- 2008-08-09select str_to_date ('08. 09.2008 ',' % m. % d. % y'); -- 2008-08-09select str_to_date ('08: 09: 30', '% h: % I: % s'); -- 08: 09: 30 select str_to_date ('08. 09.2008 08:09:30 ',' % m. % d. % Y % h: % I: % s'); -- 08:09:30 we can see that the str_to_date (str, format) Conversion Function can convert some disordered strings into date formats. In addition, it can be converted to time. For "format", see the MySQL manual. 4. mySQL Date/Time to Str (convert date/time to string) functions: date_format (Date, format), time_format (Time, format) mysql> select date_format ('2017-08-08 22:23:00 ',' % W % M % y'); + ------------------------------------------------ + | date_format ('2017-08-08 22:23:00 ', '% W % M % y') | + -------------------------------------------------- + | Friday August 2008 | + week + mysql> select date_format (' 2008 22:23:01 ',' % Y % m % d % H % I % s'); + -------------------------------------------------------- + | date_format ('2017-08-08 22:23:01 ', '% Y % m % d % H % I % s') | + ------------------------------------------------------ + | 20080808222301 | + latency + mysql> select time_format ('22: 23: 01 ', '% H. % I. % s'); + ------------------------------------- + | time_format ('22: 23: 01', '% H. % I. % s') | + ----------------------------------- + | 22.23.01 | + --------------------------------------- + MySQL date and time conversion functions: date_format (date, format), time_format (time, format) converts a date or time to a variety of string formats. It is an inverse conversion of the str_to_date (str, format) function. 5. mySQL returns the time format function of a country or region: get_format () MySQL get_format () Syntax: get_format (date | time | datetime, 'eur' | 'USA '| 'jis' | 'iso '| all examples of 'internal' MySQL get_format () usage: select get_format (date, 'USA '); -- '% m. % d. % y' select get_format (date, 'jis '); --' % Y-% m-% d' select get_format (date, 'iso '); -- '% Y-% m-% d' select get_format (date, 'eur'); --' % d. % m. % y' select get_format (date, 'internal'); -- '% Y % m % d' select get_format (datetim E, 'usa'); -- '% Y-% m-% d % H. % I. % s' select get_format (datetime, 'jis '); --' % Y-% m-% d % H: % I: % s' select get_format (datetime, 'iso '); --' % Y-% m-% d % H: % I: % s' select get_format (datetime, 'eur '); -- '% Y-% m-% d % H. % I. % s' select get_format (datetime, 'internal'); -- '% Y % m % d % H % I % s' select get_format (time, 'USA '); -- '% h: % I: % s % P' select get_format (time, 'jis'); -- '% H: % I: % s' select get_format (time, 'iso '); --' % H: % I: % S' select get_format (time, 'eur'); -- '% H. % I. % s' select get_format (time, 'internal'); -- the '% H % I % s' MySQL get_format () function has fewer opportunities in actual use. 6. mySQL patchwork Date and Time Functions: makdedate (year, dayofyear), maketime (hour, minute, second) select makedate (2001); -- '2017-01-31 'select makedate (, 32 ); -- '2014-02-01 'select maketime (2001, 30); -- '12: 15: 30' v. MySQL Timestamp function 1. mySQL obtains the current timestamp function: current_timestamp, current_timestamp () mysql> select current_timestamp, current_timestamp (); + --------------------- + ------------------- + | current_timestamp | cur Rent_timestamp () | + --------------------- + ------------------- + | 23:22:24 | 23:22:24 | + --------------------------- + ------------------- + 2. mySQL (Unix timestamp, date) conversion functions: unix_timestamp (), unix_timestamp (date), from_unixtime (unix_timestamp), from_unixtime (unix_timestamp, format) the following example: select unix_timestamp (); -- 1218290027 select unix_timestamp ('2017-08-08 '); -- 2008 select unix_timestamp ('2017-08-08 12:30:00 '); -- 2008 select from_unixtime (1218169800); -- '2017-08-09 21:53:47' select from_unixtime (1218290027 ); -- '2017-08-08 00:00:00 'selectfrom _ unixtime (2008); -- '2017-08-08 12:30:00' selectfrom _ unixtime (1218169800, '% Y % D % M % h: % I: % s % x'); -- '2017 8th August12: 30: 00 100' 3. mySQL timestamp conversion, addition, and subtraction functions: timestamp (date) -- date to timestamptimestamp (dt, time) -- dt + timet Aggregate (unit, interval, datetime_expr) -- timestampdiff (unit, datetime_expr1, datetime_expr2) -- See the example section: select timestamp ('2017-08-08 '); -- 2008-08-08 00: 00: 00 select timestamp ('2017-08-08 08:00:00 ', '01: 01: 01'); -- 2008-08-08 09: 01: 01 selecttimestamp ('2017-08-08 08:00:00 ', '10 01:01:01 '); -- 2008-08-1809: 01: 01 select timestampadd (day, 1, '2017-08-08 08:00:00'); -- 2008-08-09 08: 00: 00 select date_add ('20180101' 8-08-08 08:00:00 ', interval 1 day); -- 2008-08-09 08: 00: 00 MySQL timestampadd () function is similar to date_add (). Select timestampdiff (year, '2017-05-01 ', '2017-01-01'); ---1 select timestampdiff (day, '2017-05-01 ', '2017-01-01 '); -- 485 select timestampdiff (hour, '2017-08-08 12:00:00 ', '2017-08-08 00:00:00 '); ---12 select datediff ('2017-08-08 12:00:00 ', '2017-08-01 00:00:00'); -- 7 MySQL timestampdiff () function is better than datediff () with more features, datediff () can only calculate the number of days for the difference between two dates. Vi. MySQL timezone Conversion Function convert_tz (dt, from_tz, to_tz) selectconvert_tz ('2017-08-08 12:00:00 ',' + ',' + '); -- 2008-08-0804: 00: 00 time zone conversion can also be achieved through date_add, date_sub, timestampadd. Select date_add ('2017-08-08 12:00:00 ', interval-8 hour); -- 2008-08-08 04: 00: 00 select date_sub ('2017-08-08 12:00:00 ', interval 8 hour); -- 2008-08-08 04: 00: 00 select timestampadd (hour,-8, '2017-08-08 12:00:00 '); -- 2008 04:00:00