Conversion of dates when database queries

First of all, the Laravel framework to query and print the SQL statements, regardless of any time due to the SQL statement error, you can print first, analyze the cause of the error

① Introduction of Laravel Framework DB Class  Use Illuminate\support\facades\db;② turn on frame logging DB:: Connection ()-enablequerylog (); ③ condition, which is the SQL statement to query User:all (); ④ output prints all logs (SQL) DD (DB:: Getquerylog ());

Back to the point: the problem here is the conversion of timestamps in query statements (laravel Query builder);

First interview: The display of the time grouping, the native query method used here (Db:raw ()), the use of From_unixtime (), the timestamp converted to the date format, and the format function to change the date to be displayed and group it.

$summary = Article::query ()->select (Db::raw ("Date_format" (From_unixtime (Art_time), '%y-%m ') as time "             )->groupby (' Art_time ')            ->orderby (' art_time ', ' desc ')            ->get ();

Next, you'll need to fill out the article on the same date below the date you got it.

foreach ($summaryas$v) {            $childart = Db::select ("Select Date_format (from_ Unixtime (art_time), '%Y ') as Sumtime,art_id,art_title from blog_article where Sumtime = ". $v, time);
$v $childart ;        }

Here we start the error:

This error is simple, the query where condition can not take the previous alias, the database does not have this field will naturally error, and then modified the statement

 foreach  ( $summary  as   $v  ) {  $childart  = Db::select ("Select Date_format (From_unixtime (art_time), '%Y ') as sumtime,art_id, Art_title from Blog_article where Date_format (From_unixtime (art_time), '%y-%m ') = ".  $v ->time     print_r  (DB::            getquerylog ());            DD (  $childart    $v ->childart =  $childart  ; }

Query again, found to be empty, and then printed the SQL statement as follows:

No error, the query is empty, can be thought of is due to the where condition mismatch causes, and then found that the Date_format format should be a string, the number shown here, naturally will not query out. Then modify the SQL statement

foreach ($summaryas$v) {            $childart = Db::select ("Select Date_format (from_ Unixtime (art_time), '%Y ') as Sumtime,art_id,art_title from Blog_article where Date_format (From_unixtime (art_time), '% Y-%m ') = ' ". $v,time. "'" );             DD ($childart);             $v $childart ;        }

To do this, this issue is resolved.

Follow-up: After reading the printed SQL statements, I feel that there is no problem, after several changes to SQL statements still no information, due to the lack of understanding of the MySQL query and not how to use Date_format (), the Where condition can not be written (where Date_ Format (From_unixtime (art_time), '%y-%m ') =. $v->time), where the field is decorated in the Where condition, so the time stamp to save is too troublesome, then directly to the database to add a field to hold the time date format, Updating the contents of this field uses a simple way to manipulate MySQL using table sublime. 20 Statements were made:

Run the SQL statement into the SQL editor and run successfully. Look at this statement, and think about the previous one, I think even in the where conditions to add date_format should also be possible.

Try again: Try the following two statements directly in the SQL editor, run, successful

Update blog_article Set Summary =date_format (From_unixtime (art_time), '%y-%m ') where art_id = 8;
SELECT * from Blog_article where Date_format (From_unixtime (art_time), '%y-%m ') = ' 2018-08 ';

Suddenly found that the date_format here is a string, found the reason, to the query is empty statement added quotation marks, finally solved the problem.

Conversion of dates when database queries