date problems: my, php, or mysql issue?
| From: | Jason Murray | Date: | Sat, 26 Aug 2000 01:09:31 +0000 |
| Subject: | date problems: my, php, or mysql issue? | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-2388@lists.php.net to get a copy of this message | ||
Hi there,
Setup:
PHP Version 4.0.1.pl2
mysql Ver 6.5 Distrib 3.20.32a, for pc-linux-gnu (i686)
In the output below notice that some are 18 hours ago while others are
19 hours ago. It seems obvious to me that the problem is somehow
connected with daylight savings time, but what is not obvious is how!
strtotime() converts to a timestamp, which has nothing to do with DST,
right?
And, as far as I can see, that means there must be something wrong with
my conversion function. But I don't see a problem with it.
So I'm lost...
I can't just use plain timestamps in the table as user's will be
inputting the dates, and, it is possible, that a record is not
inserted or updated well after (perhaps even before) the actual
event takes place.
I have looked through both the PHP and MySQL docs, and nothing seems
to be able to help this situation.
I must have over-looked something?
I don't know exactly what anyone needs to try to help, so I'll just
send everything.
Thanks.
(table structure; just something stupid to develop basic date functions)
=========================================================================
mysql> describe tbl_employees;
4 rows in set (0.00 sec)
+---------+-------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-------------+----------------+
| id | smallint(5) | | PRI | 0 | auto_increment |
| name | varchar(50) | | | has no name | |
| started | date | | | 1000-01-01 | |
| stopped | date | | | 1000-01-01 | |
+---------+-------------+------+-----+-------------+----------------+
mysql> select * from tbl_employees;
7 rows in set (0.00 sec)
+----+----------------+------------+------------+
| id | name | started | stopped |
+----+----------------+------------+------------+
| 1 | Jason Murray | 2000-08-21 | 1000-01-01 |
| 2 | Mark Felicelli | 2000-04-09 | 1000-01-01 |
| 3 | Joe Koenig | 2000-01-22 | 1000-01-01 |
| 4 | Jim Comparato | 1999-02-11 | 1000-01-01 |
| 5 | Amy Comparato | 1999-02-11 | 1000-01-01 |
| 6 | Butthead | 2000-02-11 | 2000-08-23 |
| 7 | Beavis | 2000-02-15 | 2000-08-23 |
+----+----------------+------------+------------+
=========================================================================
=========================================================================
OUTPUT FROM PHP:
CURRENT EMPLOYEES:
1. Jim Comparato started Thu, February 11, 1999 (that's 561 days,
18 hours, 3 mins, and 47 secs ago.)
2. Amy Comparato started Thu, February 11, 1999 (that's 561 days,
18 hours, 3 mins, and 47 secs ago.)
3. Joe Koenig started Sat, January 22, 2000 (that's 216 days, 18
hours, 3 mins, and 47 secs ago.)
4. Mark Felicelli started Sun, April 09, 2000 (that's 138 days, 19
hours, 3 mins, and 47 secs ago.)
5. Jason Murray started Mon, August 21, 2000 (that's 4 days, 19
hours, 3 mins, and 47 secs ago.)
=========================================================================
(an example of how I compare dates...)
=========================================================================
DISPLAY CODE:
include('db.inc');
include('date.inc');
$counter = 0;
$db_ob = new _mysql_wrappers;
$db_ob->connect(1);
$db_ob->assert_select_db("employees");
echo "<STRONG>CURRENT EMPLOYEES:</STRONG>\n<BLOCKQUOTE>\n";
/* default of stopped is 1000-01-01 (it means still employeed). */
$db_ob->query("SELECT * FROM tbl_employees WHERE stopped='1000-01-01'
ORDER BY started");
$db_ob->abort_if_fail( ($db_ob->result == TRUE) );
$date_ob = new _date;
$date_ob->init();
$date_ob->set_strftime_format("%a, %B %d, %Y");
while (++$counter && ($row = $db_ob->fetch())) { /* get an object. */
$period = strtotime($row->started);
$seconds = $date_ob->init_time - $period;
$elapsed_time = $date_ob->seconds_to_days($seconds);
printf("%d. <STRONG>%s</STRONG> started %s (that's %s
ago.)<BR>\n",
$counter, $row->name, strftime($date_ob->strftime_format, $period),
$elapsed_time);
}
$db_ob->free_result();
$db_ob->close();
echo "</BLOCKQUOTE>\n";
=========================================================================
(my date class...)
=========================================================================
class _date {
var $init_time;
var $strftime_format;
var $time_now;
function seconds_to_days($arg)
{
$nr = $arg;
$str = "";
$tmp = floor($nr / 86400);
if ($tmp == 1) {
$str .= "1 day";
} else {
$str .= $tmp . " days";
}
$nr = $nr - floor($nr / 86400);
$tmp = floor($nr / 3600);
if ($tmp == 1) {
$str .= ", 1 hour";
} else {
$str .= ", " . $tmp . " hours";
}
$nr = $nr - floor($nr / 3600);
$tmp = floor($nr / 60);
if ($tmp == 1) {
$str .= ", 1 min";
} else {
$str .= ", " . $tmp . " mins";
}
$nr = $nr - floor($nr / 60);
$tmp = floor($nr);
if ($tmp == 1) {
$str .= ", and 1 sec";
} else {
$str .= ", and " . $tmp . " secs";
}
return $str;
}
function init()
{
$this->init_time = time();
$this->time_now = localtime($this->init_time, 1);
$this->strftime_format = "%c";
}
function set_strftime_format($arg)
{
$this->strftime_format = $arg;
}
} /* end class _date */
--
Jason Murray
Developer
jWeb New Media Design
1 636 928 61 32