interviewter: How to get the current date and time

select now();

select sysdate();

select current_timestamp;

select current_timestamp();

select localtime;

select localtime();

select localtimestamp;

select localtimestamp();

interviewter: If I just want to print the date , What to do with

select curdate();

select current_date;

select current_date();

select utc_date;

select utc_date();

interviewter: Now I don't want to check the date , I'd like to see the specific time

select curtime();

select current_time;

select current_time();

select utc_time;

select utc_time();

interviewter: Do you understand the conversion of date and time stamp

select from_unixtime(1649402591);	--  take UNIX The time of the timestamp is converted to the time of the normal format 
2022-04-08 15:23:11

select unix_timestamp("2022-04-08 15:23:11");	--  Time date With UNIX Return in the form of a timestamp 
1649402591

--  These two methods are used for time stamp and date conversion 

select unix_timestamp();	--  With UNIX Returns the current time in the form of a timestamp 
1649402591

--  Brief notes unix_timestamp(), If there is a time parameter, it is converted to the corresponding timestamp , Get the current timestamp without parameters , The returned results are all time stamps 

interviewter: What if I just want to get a certain part of the date and time

select now();	-- 2022-04-08 15:29:55

select year("2022-04-08 15:29:55");
select month("2022-04-08 15:29:55");
select day("2022-04-08 15:29:55");
select hour("2022-04-08 15:29:55");
select minute("2022-04-08 15:29:55");
select second("2022-04-08 15:29:55");

select dayname("2022-04-08 15:29:55");	-- Return the English name of the day of the week 

select monthname("2022-04-08 15:29:55");	-- Return the English name of the month 

select weekday("2022-04-08 15:29:55");	-- Zhou 1 yes 0, Zhou 2 yes 1...... Sunday is 6

select quarter("2022-04-08 15:29:55");	-- Returns the quarter corresponding to the date 

--  Go back to the week of the year 
select week("2022-04-08 15:29:55");
select weekofyear("2022-04-08 15:29:55");

select dayofyear("2022-04-08 15:29:55");	--  The return date is the day of the year 

select dayofmonth("2022-04-08 15:29:55");	--  The return date is the day of the month 

select dayofweek("2022-04-08 15:29:55");	--  What day of the week , Sunday is 1, Monday is 2...... Saturday is 7

-- 2022-04-08 15:29:55
select extract(year from "2022-04-08 15:29:55");	-- 2022
select extract(month from "2022-04-08 15:29:55");	-- 4
select extract(week from "2022-04-08 15:29:55");	-- 14
select extract(day from "2022-04-08 15:29:55");	-- 8
select extract(hour from "2022-04-08 15:29:55");	-- 15
select extract(minute from "2022-04-08 15:29:55");	-- 29
select extract(second from "2022-04-08 15:29:55");	-- 55
select extract(year_month from "2022-04-08 15:29:55");	-- 202204
select extract(day_hour from "2022-04-08 15:29:55");	-- 815
select extract(day_minute from "2022-04-08 15:29:55");	-- 81529
select extract(day_second from "2022-04-08 15:29:55");	-- 8152955
select extract(hour_minute from "2022-04-08 15:29:55");	-- 1529
select extract(hour_second from "2022-04-08 15:29:55");	-- 152955
select extract(minute_second from "2022-04-08 15:29:55");	-- 2955

interviewter: I want to know how many seconds there are in a certain time and how to convert , On the contrary

select time_to_sec("15:29:55")

select sec_to_time(55795);

interviewter: Will the date time interval method be used

--  Returns the difference between the given date and time INTERVAL Date time of the time period （ Up ）
select date_add("2022-04-08 15:29:55", interval 1 year);
select date_add("2022-04-08 15:29:55", interval 1 month);
select date_add("2022-04-08 15:29:55", interval 1 day);
select date_add("2022-04-08 15:29:55", interval 1 hour);
select date_add("2022-04-08 15:29:55", interval 1 minute);
select date_add("2022-04-08 15:29:55", interval 1 second);
select adddate("2022-04-08 15:29:55", interval 2 year)	--  The method and date_add equally 

--  Returns the difference between the given date and time INTERVAL Date and time of the time interval （ Down ）
select date_sub("2022-04-08 15:29:55", interval 1 year);
select date_sub("2022-04-08 15:29:55", interval 1 month);
select date_sub("2022-04-08 15:29:55", interval 1 day);
select date_sub("2022-04-08 15:29:55", interval 1 hour);
select date_sub("2022-04-08 15:29:55", interval 1 minute);
select date_sub("2022-04-08 15:29:55", interval 1 second);
select subdate("2022-04-08 15:29:55", interval 2 year)	--  The method and date_sub equally 

--  Short for :add Add ,sub reduce , The main difference between these two methods is to return the up time interval and the down time interval 

select addtime("2022-04-08 15:29:55", 10); -- Returns the time after a given time plus how many seconds , Set the second parameter to seconds 

select subtime("15:29:55", "15:20:00"); --  return time1 subtract time2 After time 

select datediff("2022-04-08", "2022-04-01"); --  return date1 subtract date2 Later date 

select timediff("15:29:55", "15:20:00"); --  return time1 subtract time2 Time interval of 

select last_day("2022-04-08 15:29:55"); --  Returns the date of the last day of the month in which the given date time is located 

select adddate("2022-04-08 15:29:55", 10);	-- Returns the time after a given date plus the number of days , Set the second parameter 

interviewter： How do dates and strings convert to each other

select date_format("20220408152955", '%Y-%m-%d'); --  Convert string to date 

select time_format("20220408152955", '%H:%i:%s')	--  Convert string to time 

select str_to_date("2022-04-08 15:29:55", '%Y-%m-%d %H:%i:%s') --  Convert time string to date