negative time() ?

[Rick]

I need to store a date prior to the unix epoch, any pointers?
--

Rick

Digital Printing
www.intelligence-direct.com

[Daniel Tryba]

Rick <ri**@intelligence-direct.com> wrote:

I need to store a date prior to the unix epoch, any pointers?

Store! Where are you storing the time?

<q src='http://nl3.php.net/manual/en/function.date.php'>
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901
20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that
correspond to the minimum and maximum values for a 32-bit signed
integer). On Windows this range is limited from 01-01-1970 to
19-01-2038.
</q>

So what platform are you on?

--

Daniel Tryba

[Pedro Graca]

Rick wrote:

I need to store a date prior to the unix epoch, any pointers?

php$ cat olddate.php
<?php
$Neil_Armstrong_lunar_landing = -14245424;

echo gmdate('r', $Neil_Armstrong_lunar_landing), "\n";
?>

php$ php olddate.php
Sun, 20 Jul 1969 02:56:16 +0000

So ... just store the number of seconds *before* unix epoch.

--
USENET would be a better place if everybody read:
http://www.expita.com/nomime.html
http://www.netmeister.org/news/learn2quote2.html
http://www.catb.org/~esr/faqs/smart-questions.html

[Rick]

Daniel Tryba wrote:

So what platform are you on?

Well, after you both pointed out there should be no problem with negative
time on linux I had a poke around and it seems to be a problem with a
function I am using to convert a Mysql datetime to a number. Forget where I
got it from, probably from the php.net documentation comments (will teach
me for being lazy)

Feed it a date prior to to '1970-01-01 00:00:00' (such as '1946-06-07
19:29:04') and it returns -1

function datetime_to_time($datetime) {
$date_time = split("\040",$datetime);
$date = split("-",$date_time[0]);
$time = split(":",$date_time[1]);
return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]);
}
--

Rick

Digital Printing
www.intelligence-direct.com

[Pedro Graca]

Rick wrote:

Feed [mktime()] a date prior to to '1970-01-01 00:00:00' (such as
'1946-06-07 19:29:04') and it returns -1

function datetime_to_time($datetime) {
$date_time = split("\040",$datetime);
$date = split("-",$date_time[0]);
$time = split(":",$date_time[1]);
return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]);
}

MySQL 4.1.1 has the function datediff() which I can't test right now.

http://dev.mysql.com/doc/mysql/en/Da...functions.html
Maybe this works:

SQL> select datediff('1946-06-07', '1970-01-01');

And you'll get the *negative* number of days till unix epoch; multiply
that by 86400 (seconds in a day) and you get a *negative* unix
timestamp that hopefully you can use directly in PHP.

--
USENET would be a better place if everybody read:
http://www.expita.com/nomime.html
http://www.netmeister.org/news/learn2quote2.html
http://www.catb.org/~esr/faqs/smart-questions.html

[Michael Fesser]

.oO(Rick)

I need to store a date prior to the unix epoch, any pointers?

Use ISO-format: YYYY-MM-DD HH:MM:SS

Micha

[John Dunlop]

Michael Fesser wrote:

Use ISO-format: YYYY-MM-DD HH:MM:SS

That isn't a representation specified by ISO8601, although
it closely resembles one and might be less odd looking to
some people than any standard representation. On his page
(see http://www.cs.tut.fi/~jkorpela/iso8601.html ), Jukka
Korpela mentions that 'people who otherwise use ISO 8601
might deviate from it here by using a space', as you did
above; but note that he recommends a combined date and time
of the day representation that strictly abides by ISO8601.

The standard clearly states that spaces are not used in its
representations (sec. 4.4). More, the time designator ('T')
may be omitted only by agreement between the parties
involved, where the date and time of the day representation
is unambiguous.

The complete, extended (that is, including separators)
representation of a calendar date* and local time of the day
is (contained in square brackets): [YYYY-MM-DDThh:mm:ss].
If readability comes second to compression though, the
basic format (that is, excluding separators) can be used:
[YYYYMMDDThhmmss].

Of course a local time itself may not always be appropriate.
To instead express a UTC time, append the UTC designator
('Z') to the time representation: [YYYY-MM-DDThh:mm:ssZ].
To express the difference between local time and UTC, append
to the time representation [±hh:mm] (extended format),
[±hhmm] (basic format), or [±hh] if the difference is an
integral number of hours. [hh] and [mm] indicate the
difference in hours and minutes respectively: e.g. [2004-10-
21T20:28:30+01:00] was when I composed this article; that
is, Thu, 21 Oct 2004 19:28:30 GMT.

There's more to it. If you have time, read

http://hydracen.com/dx/iso8601.htm
* Besides calendar dates, where the day is identified by the
day of the month of the year, the standard also specifies
representations for ordinal dates, where the day is
identified by the day of the year; and week dates, where the
day is identified by the day of the week of the year.

HAGW!

--
Jock

[Henk Verhoeven]

Michael Fesser wrote:

.oO(Rick)

I need to store a date prior to the unix epoch, any pointers?

Use ISO-format: YYYY-MM-DD HH:MM:SS

Micha

Hi,

We allways store date, time and timestamp in MySql using ISO format.
There is no problem as long as you do not want to convert them to an
integer like used by time(). See
http://www.phppeanuts.org/examples/e...?pntType=Hours
for a working example.

The framework can also convert dates like these to simple european
dates, and back, see
http://www.phppeanuts.org/examples/e...?pntType=Hours
(The user interface converts the dates. In de domain objects and the
database ISO format is used. The class StringConverter and its
superclass that does the actual conversions should work without the rest
of the framework. Same for ValueValidator to do Validations (minimum and
maximum values and lengths). If there is a demand for it we will make
these classes available as a separate download (nobody asked of it yet).

Greetings,

Henk Verhoeven,
www.phpPeanuts.org.

[Daniel Tryba]

Henk Verhoeven <ne***@phppeanutsremove-this.org> wrote:

Use ISO-format: YYYY-MM-DD HH:MM:SS

We allways store date, time and timestamp in MySql using ISO format.
There is no problem as long as you do not want to convert them to an
integer like used by time(). See
http://www.phppeanuts.org/examples/e...?pntType=Hours
for a working example.

That sound terribly inefficient. Why not use that databses date/time
format? Then there are no limits to converting also.

--

Daniel Tryba

[Michael Fesser]

.oO(John Dunlop)

Michael Fesser wrote:

Use ISO-format: YYYY-MM-DD HH:MM:SS

That isn't a representation specified by ISO8601, although
it closely resembles one and might be less odd looking to
some people than any standard representation. [...]

OK, what I had in mind were the native SQL types DATE, TIME and
TIMESTAMP, which use the above format. So it's at least ISO 9075. ;)

Micha

[Henk Verhoeven]

Daniel Tryba wrote:

That sound terribly inefficient. Why not use that databses date/time
format? Then there are no limits to converting also.

Hi Daniel,

This is some time ago i did this kind of experiments:
$result = mysql_query("SELECT * FROM testdbobjects where id = 1 ");
//(for create table statement see below)
$row = mysql_fetch_assoc($result);
print is_string($row['timestampField']) ? 'true' : 'false';
print " ";
print $row['timestampField'];

// true 2004-10-24 23:43:17

Please tell me, what's the difference between the database datatime
format and the ISO format?

Greetings,

Henk Verhoeven,
www.phpPeanuts.org.

//SQL statement for creating the testdbobjects table
CREATE TABLE testdbobjects(
id int(6) NOT NULL auto_increment,
dateField date NOT NULL default '0000-00-00',
timestampField datetime NOT NULL default '0000-00-00 00:00',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM";

[John Dunlop]

Henk Verhoeven wrote:

// true 2004-10-24 23:43:17

Please tell me, what's the difference between the database datatime
format and the ISO format?

There's quite likely a fair number of differences, which are
probably not worth listing here. I'm not familiar enough
with MySQL anyway; you yourself could find out from

http://dev.mysql.com/doc/mysql/en/DATETIME.html

and

http://hydracen.com/dx/iso8601.htm

One thing, though: ISO8601 does not use spaces in its
representations. The combined date and time of the day
representation [YYYY-MM-DD hh:mm:ss] includes a space, and
thereby forfeits compliance with ISO8601.

From a cursory glance at the MySQL docs I see that you can
specify DATETIME values as [YYYYMMDDhhmmss], which complies
with ISO8601 as long as there is mutual agreement between
the parties involved.

--
Jock

[Daniel Tryba]

Henk Verhoeven <ne***@phppeanutsremove-this.org> wrote:

Please tell me, what's the difference between the database datatime
format and the ISO format?

//SQL statement for creating the testdbobjects table
CREATE TABLE testdbobjects(
id int(6) NOT NULL auto_increment,
dateField date NOT NULL default '0000-00-00',
timestampField datetime NOT NULL default '0000-00-00 00:00',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM";

I read the phrase "We allways store date, time and timestamp in MySql
using ISO format." like: "we are storing the date/time in a iso formated
string".

--

Daniel Tryba