Conversion of one, second, and clock formats
Sec_to_time (seconds)
Returns the seconds parameter, changing to hours, minutes, and seconds, formatted with a value of ' HH:MM:SS ' or HHMMSS, depending on whether the function is in a string or a number.
Select Sec_to_time (73200);
' 20:20:00 '
Select Time_to_sec (1220);
' 00:20:20 '
Mysql> Select Sec_to_time (73200) + 0;
202000.000000
Time_to_sec (Time)
Returns the time parameter, converted to seconds.
Select Time_to_sec (' 00:20:20 ');
->1220
Select Time_to_sec (' 20:20 ');
->73200
Ii. Unix_timestamp and From_unixtime
Unix_timestamp (date)
Returns a UNIX timestamp (the number of seconds from ' 1970-01-01 00:00:00 ' GMT) if no argument is called. If Unix_timestamp () uses a
A date parameter is called, which returns the value of the second starting at ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime
A string, a timestamp, or a number in the local time in YYMMDD or YYYYMMDD format.
Select Unix_timestamp ();
1462503488
Select Unix_timestamp (' 2016-5-8 12:30:52 ');
1462681852
When Unix_timestamp is used in 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 Unix_timestamp parameter in the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is in a string
or used in a numeric context.
Select From_unixtime (1462681852);
' 2016-5-8 12:30:52 '
Select From_unixtime (1462681852) + 0;
20160506105808.000000
From_unixtime (Unix_timestamp,format)
Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the bars listed with the Date_format () function
The same modifier as the target.
Select From_unixtime (Unix_timestamp (), '%y%m%d%h%i%s%x ');
' 20160506 110443 2016 '
Select From_unixtime (Unix_timestamp (), '%y%d%m%h:%i:%s%x ');
' 20166thMay 11:21:35 2016 '
Third, Curtime () and now ()
Current_time
Returns the current time value in ' HH:MM:SS ' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.
Mysql> select Curtime ();
' 11:24:47 '
Mysql> Select Curtime () + 0;
112522.000000
Sysdate ()
Current_timestamp
Returns the current date and time in the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in the context of a number.
Select Now ();
' 2016-05-06 11:25:55 '
Select Sysdate ();
Select Current_timestamp ();
' 2016-05-06 11:25:55 '
Mysql> Select now () + 0;
20160506112611.000000
Addition and subtraction of date functions and dates in MySQL statements