DST arithmetic?
Posted: Tue Feb 27, 2007 1:27 pm
If you know the timezone, the date and the time, is it possible to mathematically determine of it's DST or not?
Basically I've almost finished this Date/Time class that doesn't use timestamps (handled 200AD-10000AD). It's wrapped around MySQL, although it only uses MySQL for the calculations that are not obvious. I've added a function that allows you to pull out the date formatted using the rules PHP defines for it's own date-time functions. Most of these can easily be "mapped" to the MySQL equivalient, some of them need some "basic" processing but there's one I can't find a way to achive in MySQL. PHP uses "I" for DST... MySQL doesn't have an equivalent from what I can see and I have no idea how you work it out
I don't want to use PHP's date() functions for this since the entire point of the class is get around the range problems of a UNIX timestamp. Here's the almost complete class:
The get(), execute() and getDateFormatted() methods are key.
Currently I'm thinking of just adding is setDST() method
I can post a unit test case if it's any help, but I doubt you need it to know the answer to this
Cheers,
d11
Basically I've almost finished this Date/Time class that doesn't use timestamps (handled 200AD-10000AD). It's wrapped around MySQL, although it only uses MySQL for the calculations that are not obvious. I've added a function that allows you to pull out the date formatted using the rules PHP defines for it's own date-time functions. Most of these can easily be "mapped" to the MySQL equivalient, some of them need some "basic" processing but there's one I can't find a way to achive in MySQL. PHP uses "I" for DST... MySQL doesn't have an equivalent from what I can see and I have no idea how you work it out
Code: Select all
<?php
/**
* A MySQL wrapper class for working with dates
* @author Chris Corbyn <chris@w3style.co.uk>
* @license GNU Lesser General Public License
*/
class MySQLDate
{
/**
* The maximum year MySQL currently deals with
*/
const MAX_YEAR = 9999;
/**
* The minimum year MySQL currently deals with
*/
const MIN_YEAR = 200;
/**
* Constant for convenience.
* Maps MySQL's %Y to YEAR
*/
const YEAR = "%Y";
/**
* Constant for convenience.
* Maps MySQL's %c to MONTH
*/
const MONTH = "%c";
/**
* Constant for convenience.
* Maps MySQL's %e to DAY_OF_MONTH
*/
const DAY_OF_MONTH = "%e";
/**
* Constant for convenience.
* Maps DAY to DAY_OF_MONTH
*/
const DAY = self::DAY_OF_MONTH;
/**
* Constant for convenience.
* Maps MySQL's %k to HOUR
*/
const HOUR = "%k";
/**
* Constant for convenience.
* Maps MySQL's %i to MINUTE
*/
const MINUTE = "%i";
/**
* Constant for convenience.
* Maps MySQL's %s to SECOND
*/
const SECOND = "%s";
/**
* Constant for convenience.
* Maps MySQL's %w to DAY_OF_WEEK
*/
const DAY_OF_WEEK = "%w";
/**
* Constant for convenience.
* Maps MySQL's %j to DAY_OF_YEAR
*/
const DAY_OF_YEAR = "%j";
/**
* Constant for convenience.
* Maps MySQL's %U to WEEK_OF_YEAR
*/
const WEEK_OF_YEAR = "%U";
/**
* Constant for convenience.
* Maps WEEK to WEEK_OF_YEAR
*/
const WEEK = self::WEEK_OF_YEAR;
/**
* Constant mapping sunday to day index 0
*/
const SUNDAY = 0;
/**
* Constant mapping monday to day index 1
*/
const MONDAY = 1;
/**
* Constant mapping tuesday to day index 2
*/
const TUESDAY = 2;
/**
* Constant mapping wednesday to day index 3
*/
const WEDNESDAY = 3;
/**
* Constant mapping thursday to day index 4
*/
const THURSDAY = 4;
/**
* Constant mapping friday to day index 5
*/
const FRIDAY = 5;
/**
* Constant mapping saturday to day index 6
*/
const SATURDAY = 6;
/**
* Constant mapping january to month index 1
*/
const JANUARY = 1;
/**
* Constant mapping february to month index 2
*/
const FEBRUARY = 2;
/**
* Constant mapping march to month index 3
*/
const MARCH = 3;
/**
* Constant mapping april to month index 4
*/
const APRIL = 4;
/**
* Constant mapping may to month index 5
*/
const MAY = 5;
/**
* Constant mapping june to month index 6
*/
const JUNE = 6;
/**
* Constant mapping july to month index 7
*/
const JULY = 7;
/**
* Constant mapping august to month index 8
*/
const AUGUST = 8;
/**
* Constant mapping september to month index 9
*/
const SEPTEMBER = 9;
/**
* Constant mapping october to month index 10
*/
const OCTOBER = 10;
/**
* Constant mapping november to month index 11
*/
const NOVEMBER = 11;
/**
* Constant mapping december to month index 12
*/
const DECEMBER = 12;
/**
* An open MySQL connection (no database access is needed)
* @var resource
*/
protected $resource;
/**
* The date in YYYY-MM-DD HH:II:SS format
* @var string
*/
protected $date;
/**
* The timezone hours from UTC
* @var int
*/
protected $tzHours = 0;
/**
* The timezone mins from UTC (on top of hours)
* @var int
*/
protected $tzMins = 0;
/**
* The sign of the timezone (1 or -1)
* @var int
*/
protected $tzDir = 1;
/**
* Maps the MySQL definitions for date units to the ones it uses for intervals.
* I have to wonder why they didn't stick with one or t'other!
* @var array
*/
protected $intervalMap = array(
self::YEAR => "YEAR",
self::MONTH => "MONTH",
self::DAY_OF_MONTH => "DAY",
self::HOUR => "HOUR",
self::MINUTE => "MINUTE",
self::SECOND => "SECOND",
self::DAY_OF_WEEK => "DAY",
self::DAY_OF_YEAR => "DAY",
self::WEEK_OF_YEAR => "WEEK"
);
/**
* Create an instance of the MySQLDate class using $connection
* @param resource The MySQL connection to use. Access to a DB is not needed.
* @param string A default date to use in YYYY-MM-DD HH:ii:ss or YYYY-MM-DD format
* @param int The timezone setting (defaults tot system timezone)
*/
public function __construct($connection, $date=null, $tz=null)
{
$this->resource = $connection;
if ($date === null) $date = $this->execute("SELECT NOW()");
$this->setDate($date);
if ($tz === null)
{
$utc_diff_secs = date("Z"); //Only place I use date(), best alternative!
if ($utc_diff_secs < 0) $prefix = "-";
else $prefix = "+";
$utc_diff_secs = abs($utc_diff_secs);
$hcf_hours = $this->getHcf($utc_diff_secs, (60 * 60));
$hours = floor($hcf_hours / (60 * 60));
$hcf_mins = $this->getHcf(($utc_diff_secs - $hcf_hours), 60);
$mins = floor($hcf_mins / 60);
$tz = $prefix . str_pad($hours, 2, "0", STR_PAD_LEFT) . ":" . str_pad($mins, 2, "0", STR_PAD_LEFT);
}
$this->setTimeZone($tz);
}
/**
* Internal implementation detail only.
* Calculates Highest-common-factors
* @param int Value
* @param int Factor
* @return int
*/
protected function getHcf($value, $factor)
{
return ($value - ($value % $factor));
}
/**
* Factory method to get a new instance
* @param resource A MySQL connection
* @param string A default date to use in YYYY-MM-DD HH:ii:ss or YYYY-MM-DD format
* @param int The timezone setting (default 0)
*/
public static function newInstance($connection, $date=null, $tz=null)
{
$instance = new self($connection, $date, $tz);
return $instance;
}
/**
* Throw an exception with message $error
* @param string The error message
*/
protected function throwError($error)
{
throw new Exception($error);
}
/**
* Execute some SQL and return the first value in the first row
* @param string SQL query
* @return string
*/
protected function execute($query)
{
$result = mysql_query($query, $this->resource)
or $this->throwError(mysql_error($this->resource));
$row = mysql_fetch_array($result);
return ($row && $row[0] !== null) ? $row[0] : false;
}
/**
* Set the current timezone
* @param int The timezone
*/
public function setTimeZone($tz)
{
if ($tz{0} == "+") $this->tzDir = 1;
elseif ($tz{0} == "-") $this->tzDir = -1;
else $this->throwError("The timezone must be formatted '+HH:MM' or '-HH:MM'. '" . $tz . "' given.");
$time_part = substr($tz, 1);
$parts = explode(":", $time_part);
if (count($parts) != 2)
$this->throwError("The timezone must be formatted '+HH:MM' or '-HH:MM'. '" . $tz . "' given.");
$hours = (int) ltrim($parts[0], "0");
if ($hours > $this->getMaxValue(self::HOUR) || $hours < $this->getMinValue(self::HOUR))
$this->throwError("The timezone must be formatted '+HH:MM' or '-HH:MM'. '" . $tz . "' given.");
$this->tzHours = $hours;
$mins = (int) ltrim($parts[1], "0");
if ($mins > $this->getMaxValue(self::MINUTE) || $mins < $this->getMinValue(self::MINUTE))
$this->throwError("The timezone must be formatted '+HH:MM' or '-HH:MM'. '" . $tz . "' given.");
$this->tzMins = $mins;
mysql_query("SET time_zone = '" . $this->escape($tz) . "'", $this->resource)
or $this->throwError(mysql_error($this->resource));
}
/**
* Get the timezone in use
* @return int
*/
public function getTimeZone()
{
$prefix = $this->tzDir == -1 ? "-" : "+";
return $prefix . str_pad($this->tzHours, 2, "0", STR_PAD_LEFT) . ":" . str_pad($this->tzMins, 2, "0", STR_PAD_LEFT);
}
/**
* Set the current date
* @param string Date in YYYY-MM-DD HH:II:SS format
*/
public function setDate($date)
{
$parts = explode(" ", $date);
$date_parts = explode("-", $parts[0]);
if (count($date_parts) != 3)
$this->throwError("Date format must be either 'YYYY-MM-DD HH:ii:ss' or 'YYYY-MM-DD'");
//Make sure we're in decimal integers
$date_parts[0] = (int) ltrim($date_parts[0], "0");
$date_parts[1] = (int) ltrim($date_parts[1], "0");
$date_parts[2] = (int) ltrim($date_parts[2], "0");
if (!empty($parts[1]))
{
$time_parts = explode(":", $parts[1]);
if (count($time_parts) != 3)
$this->throwError("Date format must be either 'YYYY-MM-DD HH:ii:ss' or 'YYYY-MM-DD'");
$time_parts[0] = (int) ltrim($time_parts[0], "0");
$time_parts[1] = (int) ltrim($time_parts[1], "0");
$time_parts[2] = (int) ltrim($time_parts[2], "0");
}
else $time_parts = array(0, 0, 0);
//Adjust if needed
if ($date_parts[0] < $this->getMinValue(self::YEAR))
$date_parts[0] = $this->getMinValue(self::YEAR);
elseif ($date_parts[0] > $this->getMaxValue(self::YEAR))
$date_parts[0] = $this->getMaxValue(self::YEAR);
if ($date_parts[1] < $this->getMinValue(self::MONTH))
$date_parts[1] = $this->getMinValue(self::MONTH);
elseif ($date_parts[1] > $this->getMaxValue(self::MONTH))
$date_parts[1] = $this->getMaxValue(self::MONTH);
if ($date_parts[1] != 2)
$max_days_in_month = $this->getDaysInMonth($date_parts[1]);
else
{
if ($this->isLeapYear($date_parts[0])) $max_days_in_month = 29;
else $max_days_in_month = 28;
}
if ($date_parts[2] < $this->getMinValue(self::DAY_OF_MONTH))
$date_parts[2] = $this->getMinValue(self::DAY_OF_MONTH);
elseif ($date_parts[2] > $max_days_in_month)
$date_parts[2] = $max_days_in_month;
if ($time_parts[0] < $this->getMinValue(self::HOUR))
$time_parts[0] = $this->getMinValue(self::HOUR);
elseif ($time_parts[0] > $this->getMaxValue(self::HOUR))
$time_parts[0] = $this->getMaxValue(self::HOUR);
if ($time_parts[1] < $this->getMinValue(self::MINUTE))
$time_parts[1] = $this->getMinValue(self::MINUTE);
elseif ($time_parts[1] > $this->getMaxValue(self::MINUTE))
$time_parts[1] = $this->getMaxValue(self::MINUTE);
if ($time_parts[2] < $this->getMinValue(self::SECOND))
$time_parts[2] = $this->getMinValue(self::SECOND);
elseif ($time_parts[2] > $this->getMaxValue(self::SECOND))
$time_parts[2] = $this->getMaxValue(self::SECOND);
//Now pad things back with zeros again...
$date_parts[0] = str_pad($date_parts[0], 4, "0", STR_PAD_LEFT);
$date_parts[1] = str_pad($date_parts[1], 2, "0", STR_PAD_LEFT);
$date_parts[2] = str_pad($date_parts[2], 2, "0", STR_PAD_LEFT);
$time_parts[0] = str_pad($time_parts[0], 2, "0", STR_PAD_LEFT);
$time_parts[1] = str_pad($time_parts[1], 2, "0", STR_PAD_LEFT);
$time_parts[2] = str_pad($time_parts[2], 2, "0", STR_PAD_LEFT);
$this->date = implode("-", $date_parts) . " " . implode(":", $time_parts);
}
/**
* Get the date in MySQL's YYYY-MM-DD HH:II:SS format
* @return string
*/
public function getDate()
{
return $this->date;
}
/**
* Get the date according to the given PHP format
* @param string The PHP date format string
* @return string
*/
public function getDatePHPFormatted($format)
{
$php_units = array(
"d", "D", "j", "l", "N", "S", "w", "z", //Day
"W", //Week
"F", "m", "M", "n", "t", //Month
"L", "o", "Y", "y", //Year
"a", "A", "B", "g", "G", "h", "H", "i", "s", //Time
"e", "I", "O", "P", "T", "Z", //Timezone
"c", "r", "U" //Full
);
$re = "~(?<!\\\\)(%|" . implode("|", $php_units) . ")~";
$cb = array($this, "cbConvertDate");
$mysql_format = preg_replace_callback($re, $cb, $format);
return $this->getDateFormatted(stripslashes($mysql_format));
}
/**
* Implementation detail only.
* A callback to use when converting between MySQL and PHP formats
* @param array PCRE backreferences
* @return string
*/
public function cbConvertDate($matches)
{
switch ($matches[0])
{
case "%": return "%%"; //Misc
case "d": return "%d"; //Day
case "D": return "%a";
case "j": return "%e";
case "l": return "%W";
case "N":
$d = $this->get(self::DAY_OF_WEEK);
return $d == 0 ? 7 : $d;
case "S": return "%D";
case "w": return (int) ltrim($this->get("%w"), "0");
case "z":
return $this->get(self::DAY_OF_YEAR) - 1;
case "W": return (int) ltrim($this->get("%u"), "0"); //Week
case "F": return "%M"; //Month
case "m": return "%m";
case "M": return "%b";
case "n": return "%c";
case "t":
return $this->getDaysInMonth($this->get(self::MONTH));
case "L": return (int) $this->isLeapYear(); //Year
case "o": return "%x";
case "Y": return "%Y";
case "y": return "%y";
case "a": return strtolower($this->get("%p")); //Time
case "A": return "%p";
case "B":
//Swatch internet time, base 10 system
$h = $this->get(self::HOUR);
$m = $this->get(self::MINUTE);
$s = $this->get(self::SECOND);
$tot_secs = ($h * 60 * 60) + ($m * 60) + $s;
return floor(($tot_secs / 86400) * 1000);
case "g": return "%l";
case "G": return "%k";
case "h": return "%I";
case "H": return "%H";
case "i": return "%i";
case "s": return "%S";
//case "e": return "N/A";
//case "I": //DST?
//case "O": GMT offset +0200
//case "P": GMT offset +02:00
//case "T": TZ abbreviation?
case "Z":
return ((60 * 60 * $this->tzHours) + (60 * $this->tzMins)) * $this->tzDir;
case "c": //Full formats
return "%Y-%m-%dT%H:%i:%s" . $this->getTimeZone();
case "r":
return "%a, %d %b %Y %H:%i:%s " . $this->getTimeZone();
//case "U": UNIXTIME
}
}
/**
* Get the date formatted according to the given MySQL date format string
* @param string Format
* @return string
*/
public function getDateFormatted($format)
{
return $this->get($format);
}
/**
* Get the value for a given unit
* @param string The unit as defined by MySQL
* @return mixed
*/
public function get($unit)
{
$parts = explode(" ", $this->getDate());
$date_parts = explode("-", $parts[0]);
$time_parts = explode(":", $parts[1]);
switch ($unit)
{
case self::YEAR:
return (int) ltrim($date_parts[0], "0");
break;
case self::MONTH:
return (int) ltrim($date_parts[1], "0");
break;
case self::DAY_OF_MONTH:
return (int) ltrim($date_parts[2], "0");
break;
case self::HOUR:
return (int) ltrim($time_parts[0], "0");
break;
case self::MINUTE:
return (int) ltrim($time_parts[1], "0");
break;
case self::SECOND:
return (int) ltrim($time_parts[2], "0");
break;
//Only query MySQL if we can't answer ourselves!
default:
$value = $this->execute(
"SELECT DATE_FORMAT('" . $this->escape($this->getDate()) . "',
'" . $this->escape($unit) . "')");
if ($value === false) $this->throwError($this->execute("SHOW WARNINGS"));
return ltrim($value, "0");
}
}
/**
* Set a unit of the date to $value
* @param string Unit of time of defined by MySQL
* @param int The value for that unit
*/
public function set($unit, $value, $date_parts=null, $time_parts=null)
{
if ($date_parts === null || $time_parts === null)
$parts = explode(" ", $this->getDate());
if ($date_parts === null) $date_parts = explode("-", $parts[0]);
if ($time_parts === null) $time_parts = explode(":", $parts[1]);
//Make sure we're not going out of range
if ($value < $this->getMinValue($unit))
$value = $this->getMinValue($unit);
elseif ($value > $this->getMaxValue($unit))
$value = $this->getMaxValue($unit);
switch ($unit)
{
case self::YEAR:
$date_parts[0] = str_pad($value, 4, "0", STR_PAD_LEFT);
break;
case self::MONTH:
$days_now = $this->get(self::DAY_OF_MONTH);
if ($days_now > $this->getDaysInMonth($value))
$date_parts[2] = $this->getDaysInMonth($value);
$date_parts[1] = str_pad($value, 2, "0", STR_PAD_LEFT);
break;
case self::DAY_OF_MONTH:
$date_parts[2] = str_pad($value, 2, "0", STR_PAD_LEFT);
break;
case self::HOUR:
$time_parts[0] = str_pad($value, 2, "0", STR_PAD_LEFT);
break;
case self::MINUTE:
$time_parts[1] = str_pad($value, 2, "0", STR_PAD_LEFT);
break;
case self::SECOND:
$time_parts[2] = str_pad($value, 2, "0", STR_PAD_LEFT);
break;
case self::DAY_OF_WEEK:
$day_of_week_now = $this->get(self::DAY_OF_WEEK);
$adjust = $value - $day_of_week_now;
return $this->add(self::DAY_OF_MONTH, $adjust);
case self::DAY_OF_YEAR:
$day_of_year_now = $this->get(self::DAY_OF_YEAR);
$adjust = $value - $day_of_year_now;
return $this->add(self::DAY_OF_MONTH, $adjust);
case self::WEEK_OF_YEAR:
$week_of_year_now = $this->get(self::WEEK_OF_YEAR);
$adjust = $value - $week_of_year_now;
return $this->add(self::WEEK_OF_YEAR, $adjust);
default:
$this->throwError(
"The class does not support setting the value for the unit [" . $unit . "]");
}
$this->setDate(implode("-", $date_parts) . " " . implode(":", $time_parts));
}
/**
* Add a negative or positive amount to the date unit
* @param string The unit as defined by MySQL
* @param int The amount to change by
*/
public function add($unit, $value)
{
$interval = $this->getIntervalUnit($unit);
$date = $this->execute(
"SELECT ADDDATE('" . $this->escape($this->getDate()) . "',
INTERVAL " . $this->escape($value) . " " . $this->escape($interval) . ")");
if ($date === false)
{
$error = $this->execute("SHOW WARNINGS");
$this->throwError($error);
}
else $this->setDate($date);
}
/**
* Get the maximum value the unit can be set to right now
* @param string the unit as defined by MySQL
* @return string
*/
public function getMaxValue($unit)
{
switch ($unit)
{
case self::YEAR:
return self::MAX_YEAR;
case self::MONTH:
return self::DECEMBER;
case self::DAY_OF_MONTH:
return $this->getDaysInMonth($this->get(self::MONTH));
case self::HOUR:
return 23;
case self::MINUTE:
return 59;
case self::SECOND:
return 59;
case self::DAY_OF_WEEK:
return self::SATURDAY;
case self::DAY_OF_YEAR:
return 366;
case self::WEEK_OF_YEAR:
return 53;
default:
return false;
}
}
/**
* Get the minimum value the unit can be set to right now
* @param string the unit as defined by MySQL
* @return string
*/
public function getMinValue($unit)
{
switch ($unit)
{
case self::YEAR:
return self::MIN_YEAR;
case self::MONTH:
return self::JANUARY;
case self::DAY_OF_MONTH:
return 1;
case self::HOUR:
return 0;
case self::MINUTE:
return 0;
case self::SECOND:
return 0;
case self::DAY_OF_WEEK:
return self::SUNDAY;
case self::DAY_OF_YEAR:
return 1;
case self::WEEK_OF_YEAR:
return 1;
default:
return false;
}
}
/**
* Get the days in the given month for this year
* @param int The month number 1 to 12
* @return int
*/
public function getDaysInMonth($month)
{
if ($month < 1 || $month > 12) $this->throwError("No such month [" . $month . "]");
else
{
switch ($month)
{
//30 days hath september, april, june and november ....
case 9: case 4: case 6: case 11:
return 30;
//all the rest have 31....
case 1: case 3: case 5: case 7: case 8: case 10: case 12:
return 31;
//except february....
case 2:
//which has 28, or 29 in a leap year
return $this->isLeapYear($this->get(self::YEAR)) ? 29 : 28;
}
}
}
/**
* Check if the given year is a leap year or not.
* This is just about 100% stolen from the Java java.util.GregorianCalendar class.
* Even the comments are stolen for my own sanity if I ever try to fully work out the logic!
* @param int The year to check
* @return boolean
*/
public function isLeapYear($year=null)
{
if ($year === null) $year = $this->get(self::YEAR);
//Only years divisible by 4 can be leap years
if (($year & 3) != 0) return false;
//Compute linear day of the 29 February of that year.
// The 13 is the number of days that were omitted in the
// Gregorian Calendar before the epoch
$julian_day = ((($year - 1) * (365 * 4 + 1)) >> 2 ) +
(31 + 29 - (((1970 - 1) * (365 * 4 + 1)) / 4 + 1 - 13));
//If that day is smaller than the Gregorian Change, the Julian rule applies:
// This is a leap year since it is divisible by 4
if ($julian_day * (24 * 60 * 60 * 1000) < 1582) return true;
//Year 4000AD is a special exception, where there will be a leap year
// The Java implementation doesn't take this into account
return (($year % 4000) != 0 && (($year % 100) != 0 || ($year % 400) == 0));
}
/**
* Convert the standard MySQL unit of time definition to the one it uses for date calculations
* @param string The unit of time as defined by MySQL
* @return string
*/
protected function getIntervalUnit($unit)
{
if (!isset($this->intervalMap[$unit]))
$this->throwError(
"Cannot add to the unit of time [" . $unit . "] as it's not in my definitions.");
$interval = $this->intervalMap[$unit];
return $interval;
}
/**
* Get a string escaped to make it safe for querying in DB
* @param string The (potentially) unsafe string
* @return string The safe string
*/
protected function escape($string)
{
return mysql_real_escape_string($string, $this->resource);
}
}Currently I'm thinking of just adding is setDST() method
I can post a unit test case if it's any help, but I doubt you need it to know the answer to this
Cheers,
d11