Filtering date data in MySQL using WHERE against a date

In a previous post I described a method of using DATE_FORMAT to sort data and search against it. That post described how you can sort your data and then use a WHERE clause against it. It basically described a 2 step process, this is alternative and does it in 1 step. There are benefits to both methods and I leave it up to you to decide which way to go.

N.B. I am sure this method is on a million different websites, I only include it on my blog so that it has both versions.

The SQL:

SELECT * FROM your_table WHERE your_timestamp > '2010-10-09 00:00:00' AND your_timestamp < '2010-10-09 23:59:59'

The Explanation:

It's quite simple really; MySQL performs a comparison where your value in your DATETIME field is greater than the minimum reference and less than the maximum. In the case above it selects all dates from a specific day (2010-10-09), but in theory you could do it on many different ranges.

I hope this helps.

Stop your website copyright information going out of date

If you like your copyright information at the bottom of your website to update itself, do not hard code the date in to the page, use PHP to do it for you.

<?php>
echo '&copy;'.date('Y');
?>

That should be more than accurate, but you could always add in the month if you want as well:

<?php>
echo '&copy;'.date('F Y');
?>

Hope this helps...

Magento snippets I find useful

I have created this page as I often find myself working on Magento projects in many different locations and needed a quick reference. You will often find, you will need to add these in to your scripts to get that level of functionality that you need for your magento customisation.

N.B. This page is ongoing and will be updated often. All snippets work on 1.4.11

Singles

//get current controller name
$this->getRequest()->getControllerName(); //returns string

//get Action Name
$this->getRequest()->getActionName(); //returns string

//get Module Name
$this->getRequest()->getModuleName(); //returns string

//get Number of items in user cart
$this->helper('checkout/cart')->getSummaryCount(); //returns number of products

//check if user is logged in
if ($this->helper('customer')->isLoggedIn()) //returns true/false

Combinations

//check if user is on home page
if ($this->getIsHomePage()) { } //returns true/false
//check for home page only works when in header.phtml
//use method below if outside of this file or
//create new instance of:
//Mage_Page_Block_Html_Header() like so:
//$newHeaderObject = new Mage_Page_Block_Html_Header();//
if($newHeaderObject ->getIsHomePage()){} // returns true/false

//alternative method
if (($this->getRequest()->getModuleName() == 'cms') && ($this->getRequest()->getActionName() == 'index')) { } //returns true/false
//use this to check if home page in other template (.phtml) files
//works by checking the module name and the action request.

Search functions against DATETIME in MySQL [using PHP]

When you are using a database for any large scale application, it is always a good idea to make a note of when things happen, i.e. when a page was created or when a page was updated. The easiest way of doing this is to pass NOW() through your SQL to your database to add the date and time to a DATETIME field, whereby you end up with data looking like this in your table:

2010-10-13 18:32:45

2010-10-13 18:28:01

2010-10-13 18:29:17

In its raw form, the SQL for performing this function will look something like this:

SELECT * FROM your_table WHERE DATE_FORMAT(column_name, '%Y-%m-%d')= 'your_date'

The SQL statement works in two stages:

1. MySQL first formats the data into your prescribed format, in my case, this was YEAR (%y), MONTH (%m), DAY (%d).
2. Now that MySQL has sorted the data internally, it performs the comparison part of the statement against your formatted data using the WHERE clause.

Of course, the internal functions that MySQL provides performs the vast majority of the hard work for you. The key to using this solution is to provide your string in the same format as the data is stored. In my case, the string I needed to run a comparison on was a date, the time wasn't a necessary part of the clause, so I provided:

'2010-10-13'

So my SQL looked like a little bit like this:

SELECT * FROM my_dates WHERE DATE_FORMAT(my_date_time, '%Y-%m-%d')= '2010-10-13'
(Notice the hyphen as the separator, this may differ in your case)

%Y = 4 digit numeric YEAR,
%m = 2 digit numeric MONTH,
%d = 2 digit numeric DAY

There are many other options for date formatting, all of which can be found here on the MySQL website:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

I have also written a different method of extracting data using a date, you can find that here.

I hope this helps...