Summary of date/time functions for MySQL and Oracle

Read Catalogue

• Objective
• string and date/time reciprocal conversion function
• Date/Time calculation function

Objective

The date/time of this article is all formatted as "2016-01-01 01:01:01"; monitor_time is the database table field;

string and date/time Mutual conversion function Oracle

Date/Time -to-string function: To_char (t.monitor_time, ' Yyyy-mm-dd hh24:mi:ss ') as Monitortime

string to date/Time function : to_date ("2016-01-01 01:01:01", ' Yyyy-mm-dd hh24:mi:ss ')

Mysql

Date/Time -to-string function: Date_format (t.monitor_time, '%y-%m-%d%h:%i:%s ') as Monitortime

string to date/Time function : Str_to_date ("2016-01-01 01:01:01", '%y-%m-%d%h:%i:%s ')

Date/Time Calculation function Oracle

Add or subtract one hour:

Sysdate+1/24

Sysdate-1/24

Add or subtract one day:

Sysdate+1

Sysdate-1

Increase or decrease January:

Add_months (sysdate, 1)

Add_months (Sysdate,-1)

Increase or decrease the first quarter:

Add_months (Sysdate, 3)

Add_months (Sysdate,-3)

Increase or decrease one year:

Add_months (Sysdate, 12)

Add_months (Sysdate,-12)

Mysql

Add or subtract one hour:

Date_sub (Sysdate (), Interval-1 Hour)

Date_sub (Sysdate (), Interval 1 hour)

Add or subtract one day:

Date_sub (Sysdate (), Interval-1 Day)

Date_sub (Sysdate (), Interval 1 day)

Increase or decrease January:

Date_sub (Sysdate (), Interval-1 month)

Date_sub (Sysdate (), Interval 1 month)

Increase or decrease the first quarter:

Date_sub (Sysdate (), interval-3 month)

Date_sub (Sysdate (), Interval 3 month)

Increase or decrease one year:

Date_sub (Sysdate (), Interval-1 year)

Date_sub (Sysdate (), Interval 1 year)

Summary of date/time functions for MySQL and Oracle