MySQL Stored Procedure Programming

Date and Time Functions

Date and time functions operate on MySQL date-time data types such as DATE and DATETIME.

ADDTIME

    date1=ADDTIME(date2,time_interval)

ADDTIME adds the specified time interval to the date-time provided and returns the amended date. Time intervals are specified in the format hh:mm:ss.hh, so you can add any time interval down to one-hundredth of a second.

    SET var1=NOW(  );                                 →  2005-07-21 18:56:46
    SET var2=ADDTIME(NOW(  ),"0:00:01.00");           →  2005-07-21 18:56:47
    SET var3=ADDTIME(NOW(  ),"0:01:00.00");           →  2005-07-21 18:57:46
    SET var4=ADDTIME(NOW(  ),"1:00:00.00") ;          →  2005-07-21 19:56:46

CONVERT_TZ

    datetime1=CONVERT_TZ(datetime2,fromTZ,toTZ)

This function converts a date-time value from one time zone to another. The valid time zone values can be found in the table mysql.time_zone_name.

You may have to load the MySQL time zone tables; for instructions, see the MySQL manual section “MySQL Server Time Zone Support.”

CURRENT_DATE

    date=CURRENT_DATE(  )

CURRENT_DATE returns the current date. It does not show the time.

    SET var1=CURRENT_DATE(  );    →  2005-07-21

CURRENT_TIME

    time=CURRENT_TIME(  )

CURRENT_TIME returns the current time. It does not show the date.

    SET var1=CURRENT_TIME(  );    →  22:12:21

CURRENT_TIMESTAMP

    timestamp=CURRENT_TIMESTAMP(  )

CURRENT_TIMESTAMP returns the current date and time in the format yyyy-mm-dd hh:mm:ss.

    SET var1=CURRENT_TIMESTAMP(  );    →  2005-07-21 22:15:02

DATE

    date=DATE(datetime)

DATE returns the date part of a date-time value.

 SET var1=NOW( ); → 2005-07-23 ...