Time issues in MySQL

1. Comparison of time

Using the time function to_date in Oracle is a habit. in Oracle, the addition and subtraction of time is also very simple. You can directly add and subtract time. There are many time functions in MySQL, which are very free.

MySQL cannot directly compare the time.> or <
You can only convert it to a number to compare the size.

In Oracle, trunc (sysdate, 'D') is often used to intercept to days or hours.
In MySQL, date_format () can be used instead. Returns a string in the time format, which is also a time string.
In MySQL, strings that conform to the time format can be treated as the date type.
From today to day,
Mysql> select date_format (now (), '% Y % m % D ');

If the time is accurate, you need to convert the time to the unix_timestamp and the millisecond value to compare the size.

Unix_timestampFor example:

Select * From projectmanager
Where (unix_timestamp (now ()-unix_timestamp ('2017-4-10 21:14:20 ')> 0;

Date calculation:

1. The time addition and subtraction function is
:
Add adddate () and subtract subdate ();
Select subdate (now (), interval 60 day); time 60 days ago
Select adddate (now (), interval 60 day); 60 days later

2. If the time difference is compared, you can add or subtract the value as follows:

1) query data that is 25 minutes earlier than the current time Select now ()-interval 25 minute; 2) query data of the current time of 25 minutes Select now () + interval 5 minute; 3. addtime addition and subtraction of time: When we are giving now () +-a time, we should actually understand it as follows: + 1/+ 01: add 1 second + 101/+ 0101: Add 1 minute 1 second + 10101/+ 010101: Add 1 hour 1 minute 1 second + 1010101/+ 01010101: add 1 day 1 minute 1 second + 101010101/+ 0101010101: Add 1 day 1 minute 1 second + 1101010101/+ 010101010101: Add 1 year January 1 day 1 minute 1 second, it should be noted that the year may be four digits (if there is no high position, it will be zero): 00010101010101 so for the problem of the front side: Pull the records added in the past three hours, you can write the SQL statement as follows: Select ID, title, content, addtime from tablename_tbl where addtime> = now ()-30000 and addtime <= now () 4. Another method of date_add and date_sub is provided by MySQL: Date_add (Add specified time interval to date), date_add (date, interval expr type)  Date_sub (Subtract the specified time interval from the date), date_sub (date, interval expr type) (similar to date_add)

Select ID, title, content, addtime from tablename_tbl where addtime> = date_sub (now, interval 3 hour) and addtime <now () Type values: microsecond, second, minute, hour, day, week, month, quarter, year, second_microsecond, hour, hour_minute, day_microsecond, day_second, day_minute, day_hour