MySQL date usage Summary

MySQL date usage Summary

 

Count the number of records in the current month by month
Select PID, date_format (adddate,'% Y-% C'), Count (*) as ABC from t_tone_log group by PID, date_format (adddate,'% Y-% C')

% Y year % C month

Join and query (find out what T1 does not have in T2)
Select * From t_company_singer_song as T1 left join t_song_new as T2 on (
T1.song _ id = t2.id)
Where t2.id is null

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~

Technical documents:

Date_format (date, Format)
Format the date value based on the format string. The following modifier can be used in the format string: % m month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (HH: mm: ss [AP] m)
% T time, 24 hours (HH: mm: SS)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P am or PM
% W days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

All other characters are not interpreted and copied to the result.

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 97 sat 04 10 Oct 277' 
Mysql> select date_format ('1997-10-04 22:23:00',
'% H % K % I % R % T % S % W');
->'22 22 10 10:23:00 PM 22:23:00 6' 
In mysql3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.

Time_format (time, Format)
This is used like the preceding date_format () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds.

Other modifiers generate a null value or 0.
Curdate ()

Current_date
To'YYYY-MM-DD'Or the date value returned in yyyymmdd format depends on whether the function is used in a string or numeric context.
Mysql> select curdate ();
->'1997-12-15' 
Mysql> select curdate () + 0;
-> 19971215

Curtime ()

Current_time
To'Hh: mm: SS'Or the current time value is returned in hhmmss format, depending on whether the function is used in a string or a number context.
Mysql> select curtime ();
->'23:50:26' 
Mysql> select curtime () + 0;
-> 235026

Now ()

Sysdate ()

Current_timestamp
To'YYYY-MM-DD hh: mm: Ss.'Or returns the current date and time in yyyymmddhhmmss format, depending on whether the function is in a string or in a number
The context is used.
Mysql> select now ();
->'1997-12-15 23:50:26' 
Mysql> select now () + 0;
-> 19971215235026

Unix_timestamp ()

Unix_timestamp (date)
If no parameter is called, a Unix Time Stamp (from'00:00:00'The number of seconds since GMT ). If unix_timestamp () is called with a date parameter, it returns'00:00:00'The second value starting with GMT. Date can be a number of a date string, A datetime string, a timestamp, or a 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 in a timestamp column, the function will directly accept the value, without the implicit "string-to-Unix-timestamp" transformation www.knowsky.com.

From_unixtime (unix_timestamp)
To'YYYY-MM-DD hh: mm: Ss.'Or, in the yyyymmddhhmmss format, return the value represented by the unix_timestamp parameter, depending on whether the function is used in a string or a number context.
Mysql> select from_unixtime (875996580 );
->'1997-10-04 22:23:00' 
Mysql> select from_unixtime (875996580) + 0;
-> 19971004222300

From_unixtime (unix_timestamp, Format)
Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by 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)
Returns the seconds parameter, which is converted to hour, minute, and second.'Hh: mm: SS'Or hhmmss formatting depends on whether the function is used in a string or numeric 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, which is converted to seconds.
Mysql> select time_to_sec ('22:23:00');
-> 80580
Mysql> select time_to_sec ('00:39:38');
-> 2378