Dealing With Dates

A common task is to add date parameters to a report. Here's an example:

-- Get Purchases Between Two Dates
-- VARIABLE: { name: "start_date", display: "Start Date", type: "date" }
-- VARIABLE: { name: "end_date", display: "End Date", type: "date" }

SELECT * FROM Purchases 
WHERE PurchaseDate BETWEEN "{{ dbdate('start_date') }}" AND "{{ dbdate('end_date') }}";

Since the variable types were set to "date", any input will be parsed with PHP's strtotime function and formatted to "YYYY-MM-DD HH:MM:SS" format before being passed into the report. This means you can enter "-1 week" and "now" as values if you want.

It's important when outputting the variables to use the dbdate() function. This will handle timezone conversions (described below) and formatting.

If you want a different format than the default "YYYY-MM-DD HH:MM:SS", you can specify that too:

VARIABLE: { name: "start_date", display: "Start Date", type: "date", format: "m/d/Y" }

Timezones

This all works great as long as the report framework is in the same timezone as your database servers. If, for example, the report framework is on the West Coast and your database is on the East Coast and someone enters "-1 hour" as the time, it's not clear which timezone it would base use as a base. In this particular case, it would use West Coast time since that is where the report framework is installed.

In config/config.php, you can define time offsets for any database. Let's assume our production database is 3 hours ahead and our dev database is 1 hour behind the host running the report framework. The config would look like this:

return array(
...
	'environments'=>array(
		'production'=>array(
			'mysql'=>array(
				'host'=>'http://example.com',
				'user'=>'root',
				'time_offset'=>3
			)
		),
		'dev'=>array(
			'mysql'=>array(
				'host'=>'http://example-dev.com',
				'user'=>'root',
				'time_offset'=>-1
			)
		)
	),
...
);

Now if a user enters "2012-05-10 12:00:00" in a date variable, it will be rendered "2012-05-10 15:00:00" on production and "2012-05-10 11:00:00" on dev. Note, this only works when outputting dates using the "dbdate" Twig function.

Things get slightly more complicated when joining between databases that have different timezone offsets. Suppose your "statistics" database has a 4 hour time offset and your main database has a 3 hour offset. The "dbdate" function has an optional second parameter to specify what database to pull the offset from. Here is an example (assume the database "nodb_mysql" has connection info, but no database selected).

-- Dates With Multiple Databases in Different Timezones
-- VARIABLE: { name: "some_date", display: "Some Date", type: "date" }
-- DATABASE: nodb_mysql

SELECT
	table1.*,
	table2.*
FROM
	{{ environment.mysql.database }}.some_table AS table1,
	{{ environment.statistics.database }}.some_other_table AS table2
WHERE
	table1.StartDate > "{{ dbdate('some_date','mysql') }}"
	OR table2.StartDate > "{{ dbdate('some_date','statistics') }}"

Here is the generated SQL if the date "2012-05-10 12:00:00" was passed in to the variable:

SELECT
	table1.*,
	table2.*
FROM
	MyDatabase.some_table AS table1,
	StatisticsDatabase.some_other_table AS table2
WHERE
	table1.StartDate > "2012-05-10 15:00:00"
	OR table2.StartDate > "2012-05-10 16:00:00"