Dates and Timezones

SmartDatabase Constructor:

In the SmartDatabase constructor, you should pass the option for 'default-timezone' - you can use something like the value of php's function "date_default_timezone_get()" to use the current system's default timezone (see php's date_default_timezone_set() function for more info). This is a long timezone name (i.e. "America/Indiana/Indianapolis") that all dates will be converted to when they are fetched from the database. Using a timezone will make sure ALL dates are converted to UTC time when stored in the database. Then you can switch this "default-timezone" anytime to get the dates in a different timezone with ease).

The only column data types affected by timezones are Timestamp and Datetime (not Time or Date columns)

If no default is specified, then no timezone conversion will take place at all and dates will be stored exactly as provided. Every date is *assumed* to be in system time, and they are stored/fetched as-is. This is NOT recommended. You should always want your times stored as GMT. Use a default timezone, even if it is just "UTC"

DbManager_MySQL:

In the DbManager constructor, you should set the 'timezone' value to '+0:00'. An empty value here will use system default time (not recommended). MySQL will automatically convert Datetime columns to GMT based on the default timezone set here (but not Timestamps. This is a core component of the MySQL datatypes). It's recommended you use '+0:00' here so Timestamps will NOT be converted when stored at the database, this way, the SmartDatabase can handle Timestamps and Datetimes for you automatically.

Note- you should use hard numbers like '+0:00' for this setting. You *can* use timezone abbreviations, but they must be defined in MySQL and they may not be installed on your system. Using numbers is foolproof.

SmartColumn and SmartDatabase:

The SmartColumn and SmartDatabase Classes both contain a variable $DefaultTimezone:

string — If set, ALL datetime/timestamp values stored in this column (or, for the entire database if set on the Database level) will be converted to UTC time for storing in the DB, then returned in whatever is set as the DefaultTimezone. Leaving this empty will use system time and no dates will be touched (not recommended, as you have little control over timezone management going forward). There is a SmartDatabase->DefaultTimezone, and also a SmartColumn->DefaultTimezone. If both values are set, the column's default will take precedence.

Init Example:

Here are the basics if you want Chicago time, for example:

$dbManager = new DbManager_MySQL('localhost', 'user', 'pass', 'db-name', ['timezone'=>'+0:00']); //always use '+0:00'
$db = new SmartDatabase($dbManager, dirname(__FILE__).'/test.xml');

//set these to the timezone of the USER. these are the dates the user sees
date_default_timezone_set('America/Chicago');
$db->DefaultTimezone = 'America/Chicago';

//example saving
$date1 = gmdate("Y-m-d H:i:s T");
$row['Timestamp'] = $date1; //stored as UTC
$row['Datetime'] = $date1; //stored as UTC

//example fetching
$date2 = $row['Timestamp'](); //fetched as CST (or CDT)
$date3 = $row['Datetime'](); //fetched as CST (or CDT)

Timezone Support Release Notes (raw)

MAJOR updates to SmartDB for full timezone support. Updates are reverse compatible so if no Timezone is defined on the SmartDb, no special date modifications take place at all (this was the previous functionality).

DbManager:

  • Ddded new "timezone" option that, if set, will automatically query: "SET time_zone = XXXX" when connection is opened. this is not set by default for reverse compatibility.
  • We will be setting this "timezone" option to "+0:00" though so Timestamp columns will NOT be modified when storing/fetching values from MySQL so we can use Timestamp and Datetime columns completely interchangeably with Timezone support (MySQL will convert these automatically if +0:00 is not used). We will make "+0:00" the default value eventually.

SmartDb:

  • Added new "DefaultTimezone" variables on the SmartDatabase/SmartColumn classes for optionally defining a timezone that all Datetime and Timestamp column data will be returned as (column DefaultTimezone takes precedence if set, then database DefaultTimezone if set). This will not affect Date or Time columns.
  • If DefaultTimezone is in use, any dates will automatically be converted to GM time for storing in the database. You should ALWAYS include a timezone abbreviation when setting dates now. If no timezone is specified, the date is *assumed* to be PHP's default "system time" (ref php's date_default_timezone_set() function) and the date will be converted to GMT time for database storing.
  • All Datetime and Timestamp columns will have the timezone abbreviation appended as part of the returned data always.
  • Added new "IsTimezoneColumn" for column types that should be automatically converted to the specified timezone. Currently this option applies to all timestamp and datetime columns (NOT time or date columns).
  • ALL data SET on any timezone column is now first "Normalized" to a proper date, or will be set to NULL if date cannot be parsed.
  • ALL data RETURNED on aggregate functions, GetColumnValues() is now first "Normalized" to verify data type and limits
  • ALL data used in LookupAssoc arrays is now first "Normalized" to verify data type and limits (VerifyLookupAssoc now changes values IN LINE depending on what is returned from VerifyValueType())