• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 77
  • Last Modified:

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....
0
CalmSoul
Asked:
CalmSoul
  • 4
  • 3
1 Solution
 
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
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
Dave BaldwinFixer 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now