mysql query by date format

This is my date format in MySQL database

2017-12-22T13:32:48Z

I want to pull all rows from two week prior to today (minus) 7 days.

For example

today is "02/09/2018" I want to get rows from Jan 18 to Feb 1....
LVL 5
CalmSoulAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
This is the basic idea.  I use the PHP date functions to create the dates I want and then include them in the SQL string.
<?php 
$stdate = date("Y-m-d",mktime(0, 0, 0, date("m"), date("d")-14, date("Y")));
$endate = date("Y-m-d",mktime(0, 0, 0, date("m"), date("d")-7, date("Y")));
$sql = "SELECT * FROM `table` WHERE `startdate` > '$stdate' AND `enddate` < '$endate'";
$result = $link->query($sql);
// then process the resulting data
 ?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
In a MySQL Date field, "2017-12-22T13:32:48Z" would have to be "2017-12-22 13:32:48".  The only way to use your format is in a varchar or text field.  Unfortunately, MySQL will only do date comparison arithmetic and logic on Date fields.
0
 
CalmSoulAuthor Commented:
Thats fine, I can change that, assume I have that what will be the logic ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dave BaldwinFixer of ProblemsCommented:
I normally use something like this.
SELECT * FROM `table` WHERE `date` > '2018-01-18' AND `date` < '2018-02-02'

Open in new window

Note that I use the 'next' day because it includes all of the previous day because the change occurs at "2018-02-02 00:00:00".
0
 
CalmSoulAuthor Commented:
Actually, I was thinking if there is an automated way to do this not hard-coded dates, I want to grab this data in re-occurring basis
0
 
Dave BaldwinFixer of ProblemsCommented:
Certainly.  I use PHP to do this all the time.  I don't know any automated way for MySQL to do it.  There might be but I don't know it.
0
 
CalmSoulAuthor Commented:
can you share your php code? I will covert it
0
All Courses

From novice to tech pro — start learning today.