Mysql date type size comparison --- pull records for a given period of time _ MySQL

Mysql date type size comparison --- pull records for a given period of time bitsCN.com

We know that in mysql, the date type has many forms, such as date, datetime, and timestamp. Consider the following scenario:

Pull the content of a given time period by time period, then we have to use the date type comparison.

Table structure: tablename_tbl

+ ----------------- + -------------- + ------ + ----- + ------------------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ----------------- + -------------- + ------ + ----- + ------------------- + ---------------- +
| Id | mediumint (8) | NO | PRI | NULL | auto_increment |
| Title | varchar (40) | NO | NULL |
| Content | mediumtext | NO | NULL |
| Addtime | timestamp | NO | CURRENT_TIMESTAMP |

Here, addtime is of the timestamp type. now we need to pull the newly added records in the past three hours. we can compare the unix timestamp and the date;

First, check the first type: unix timestamp comparison.

Mysql built-in function: UNIX_TIMESTAMP ()

If no parameter is called, a number of seconds (unsigned integer) from '2017-01-01 00:00:00 'to the current time is returned by default ). If you use date to call unix_timestamp (), it will return the number of seconds from '2017-01-01 00:00:00 'to the time point represented by date; date can be a date string, a datetime string, a timestamp, or a number in the yymmdd or yyymmdd format of the local time.

The SQL statement can be written as follows:

Select id, title, content, addtime from tablename_tbl where UNIX_TIMESTAMP (addtime)> = UNIX_TIMESTAMP ()-10800 and UNIX_TIMESTAMP (addtime) <= UNIX_TIMESTAMP () // 10800 is the number of seconds in 3 hours.

Method 2: Date Comparison

First, you can directly use the +-operation:

In this method, you must note that the meaning of a time date value plus/minus a value.

Select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 13:08:45 |
+ --------------------- +

Through the now function, we can get the current time, and then see:

Select now (), now () + 1;
+ --------------------- + ----------------------- +
| Now () + 1 |
+ --------------------- + ----------------------- +
| 13:10:15 | 20130315131016.000000 |
+ --------------------- + ----------------------- +

Select now (), now ()-1;
+ --------------------- + ----------------------- +
| Now ()-1 |
+ --------------------- + ----------------------- +
| 13:14:18 | 20130315131417.000000 |
+ --------------------- + ----------------------- +

After these two statements are executed, the second column indicates adding/minus one second at the current time. Note that the second column indicates the form:

20130315131016.000000 (yyyy mm dd hh mm ss.000000): When you add or subtract a value to now (), the representation of the result is different from that of now (). let's look at it again:

Select now (), now ()-01;
+ --------------------- + ----------------------- +
| Now ()-01 |
+ --------------------- + ----------------------- +
| 13:15:05 | 20130315131504.000000 |
+ --------------------- + ----------------------- +

Select now (), now () + 01;
+ --------------------- + ----------------------- +
| Now () + 01 |
+ --------------------- + ----------------------- +
| 13:15:11 | 20130315131512.000000 |
+ --------------------- + ----------------------- +

It's strange to see that, isn't it? how can I directly add 0 to the front of the number? Isn't the number of seconds added or subtracted? (I thought so before), the execution result is also added to now (), minus 1 second;

Select now (), now () plus 101;
+ --------------------- + ----------------------- +
| Now () + 101 |
+ --------------------- + ----------------------- +
| 13:17:14 | 20130315131815.000000 |
+ --------------------- + ----------------------- +

Select now (), now ()-101;
+ --------------------- + ----------------------- +
| Now ()-101 |
+ --------------------- + ----------------------- +
| 13:17:22 | 20130315131621.000000 |
+ --------------------- + ----------------------- +

We can compare the execution results of these two statements and compare the two columns, we can find that we add 1 minute, 1 second to the now () time, minus 1 minute, 1 second; let's look at another example.

Select now (), now () plus 0101;
+ --------------------- + ----------------------- +
| Now () + 0101 |
+ --------------------- + ----------------------- +
| 13:19:34 | 20130315132035.000000 |
+ --------------------- + ----------------------- +

Select now (), now ()-0101;
+ --------------------- + ----------------------- +
| Now ()-0101 |
+ --------------------- + ----------------------- +
| 13:19:38 | 20130315131837.000000 |
+ --------------------- + ----------------------- +

Is it strange that these two statements also add/subtract 1 minute and 1 second for the current time. Let's look at an example that combines year, month, and day:

Select now (), now () plus 00010203040506;
+ --------------------- + ----------------------- +
| Now () + 00010203040506 |
+ --------------------- + ----------------------- +
| 13:21:50 | 20140518172656.000000 |
+ --------------------- + ----------------------- +

Select now (), now ()-00010203040506;
+ --------------------- + ----------------------- +
| Now ()-00010203040506 |
+ --------------------- + ----------------------- +
| 13:21:54 | 20120112091648.000000 |
+ --------------------- + ----------------------- +

Here we can see the meaning of +-. the above two statements are: add or subtract the current date: 1 year, 2 months, 3 days, 4 hours, 5 minutes, and 6 seconds.

Therefore, when we give now () +-a time, we should understand it as follows:

+ 1/+ 01: 1 second

+ 101/+ 0101: 1 minute plus 1 second

+ 10101/+ 010101: 1 hour, 1 minute, 1 second

+ 1010101/+ 01010101: 1 day, 1 minute, 1 second

+ 101010101/+ 0101010101: plus January 1, January 1 days, 1 minute, 1 second

+ 1101010101/+ 010101010101: Add 1-year, January 1-Day, 1-minute, 1-second. Note that this part of the year can be four digits (if it is not high, it will be zero): 00010101010101

(Subtraction is similar)

So for the front-side problem: 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 ()

Another method is provided by mysql:

Date_add (add specified time INTERVAL to date), DATE_ADD (date, INTERVAL expr type)

DateA parameter is a valid date expression.ExprThe parameter is the time interval you want to add.

Type value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Date_sub (minus the specified time INTERVAL from date), DATE_SUB (date, INTERVAL expr type) (usage is similar to date_add)

SQL can be written as follows:

Select id, title, content, addtime from tablename_tbl where addtime> = date_sub (now, INTERVAL 3 HOUR) AND addtime <now ()

(End)

BitsCN.com