Skip to main content

Posts

Showing posts from February, 2012

Relative Date Ranges: Current Week of Prior Year

A handy feature in reporting applications is the ability to query data using relative date ranges. A relative date range is nothing more than a predefined start and end time offset some manner from the current date/time, but the specifics are hidden from the end-user behind a human readable label. For example, if you had a database full of access logs and wanted to query for a list of login failures that happened yesterday you could write:<?php $yesterday = sprintf("BETWEEN '%s 00:00:00' AND '%1\$s 23:59:59'", date("Y-m-d", strtotime("-1 day"))); $query = "SELECT username, tstamp, ip_address FROM failed_logins WHERE tstamp $yesterday";The user could select "Yesterday" from a list, and the code would dynamically build the query accordingly.Alternatively, you could write it entirely in SQL using MySQL's date and time handling functions. It looks a bit messier, but is just as effective:SELECT ... BETWEEN …