There are many methods for time operations in SQL. Here we summarize several methods, such as setting the date field to int (10) When I made a database myself to save the unix_timestamp of the date, adding or subtracting values at last is much more convenient, because we use a time integer to compare values. If you need them, you can choose any of the following methods.
There are many methods for time operations in SQL. Here we summarize several methods, such as setting the date field to int (10) When I made a database myself to save the unix_timestamp of the date, adding or subtracting values at last is much more convenient, because we use a time integer to compare values. If you need them, you can choose any of the following methods.
The simplest method is to use the date_format function for comparison.
The Code is as follows: |
|
* From tb where c> date_format ('2017-07-06 ',' % Y % m % D') and c <= date_format ('2017-07-09 ', '% Y % m % D '); Select * from tb where c> date ('2017-07-07 ') and c <date ('2017-07-09 ')
|
STATDAY is a 2006031001 date data field.
The Code is as follows: |
|
Select * from LOGINSTAT where STATDAY> date_format (curdate ()-1, '% Y % m % D') and STATDAY> = date_format (curdate (), '% Y % m % D '); |
Or:
The Code is as follows: |
|
Select * from LOGINSTAT where STATDAY> date_format (curdate ()-1, '% Y % m % d % H') and STATDAY> = date_format (curdate (), '% Y % m % d % H '); |
Other usage:
The Code is as follows: |
|
Select * from LOGINSTAT where STATDAY> = date_format ('$ date1', '% Y % m % d % H') and STATDAY <date_format (' $ date ', '% Y % m % d % H ') |
The saved time format is 18:08:08. Now we need to obtain a date from a result set rs. I first use the rs. getDate () method to obtain the time. The result is only year, month, and day, and the hour, minute, and second cannot be obtained. The final solution is:
The Code is as follows: |
|
Date time1 = new Date (rs. getTimestamp ("pub_time"). getTime ()); SimpleDateFormat formattime = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss "); String pubtime = formatime. format (time1 ); |
The obtained pubtime is String type. In SQL statements, use the mysql time function date_format ('time', 'format') for conversion:
The Code is as follows: |
|
String sqlstr = "select * from do_document where pub_time |
Then execute the SQL statement to find the records that meet the conditions.
Share 3
1.
The Code is as follows: |
|
SELECT * FROM table name WHERE field name BETWEEN 'yyyy-MM-1 'AND 'yyyy-MM-30 ';
|
You can use the date and time functions to further correct the given date.
Datetime and date data can be directly compared. During comparison, datetime data is automatically converted to date data.
2. Function
The Code is as follows: |
|
Select * From table Where year (Date Field name) = 2007 and month (Date Field name) = 6 and day (Date Field name) = 10 |
I usually use the following methods:
What is the UNIX_TIMESTAMP function of MySQL? Gu ti? Parameters ????? Br/> for Example
The Code is as follows: |
|
UNIX_TIMESTAMP ('2017-08-08 20:08:08 '); UNIX_TIMESTAMP ('2017-08-08 '); |
Is the return value an integer? ? Treasure disaster recovery benzene deployment? P
Instance
The Code is as follows: |
|
Select * from table where createtime> unix_timestamp ('2017-1-1 12:12:12 '); |