If one were to actually "RTFM" in reference to the mysql manual.

Freely available at: http://dev.mysql.com/doc/refman/4.1/en/index.html [or substitute 5.0 for us hardcore mysql'ers ;-) mysql5 is sweeeeet. ]

Heres an interesting, difficult to find page [joke]

Oh check this out!



If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 5.10.8, "MySQL Server Time Zone Support".

       -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
       -> 875996580
When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit "string-to-Unix-timestamp" conversion. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0, but please note that only basic range checking is performed (year from 1970 to 2037, month from 01 to 12, day from 01 from 31).

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values. Here is an example, using TIMESTAMP values in the CET time zone:

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
|                            1111885200 |
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
|                            1111885200 |
mysql> SELECT FROM_UNIXTIME(1111885200);
| FROM_UNIXTIME(1111885200) |
| 2005-03-27 03:00:00       |
If you want to subtract UNIX_TIMESTAMP() columns, you might want to cast the result to signed integers. See Section 12.8, "Cast Functions and Operators".


Couldn't have done better if I had copied it verbatim!

Haha actually, I did nearly ;-)

No cute extracting of substrings within a mysql datetime timestamp and calculating a Epoch timestamp..

No sirreeebob!

Just one function. Returns seconds since the Unix Epoch GMT+0.


i think so too...

Tell you what, just as a tip from one supreme ruler of codecutting h4x0r developer to a budding learner:

If you are interested in looking up what sort of functions/algorithms are available for you to use on post-processing your data for a mysql result.

I'd suggest looking at something really exotic and obscure like............


;-) I know I know, it seems a strange philosophy but these champions who work on the docs for these projects are the true heros of our modern world. Not Americans, nor the poor sods who are sent to the Middle East deceived in thinking they are promoting freedom but in reality are creating a strong business for the Bush administration and their oil resource driven ambitions.

Its a terrible world isn't it?

As I was saying, thank God/Allah/Buddah/*insert deity here* that the mySQL and PHP doc's writers are so talented and organised!

Ta ta !!

----- Original Message ----- From: "Gerry Danen" <[EMAIL PROTECTED]>
To: "xkorakidis" <[EMAIL PROTECTED]>
Cc: <php-db@lists.php.net>
Sent: Monday, January 30, 2006 8:34 AM
Subject: Re: [PHP-DB] timestamp value management

I use this function:

function xlate_datetime($in_field, $in_format)
// 2005-10-02 18:05:52
// 0123456789012345678

$year = substr( $in_field,  0, 4 );
$month = substr( $in_field,  5, 2 );
$day = substr( $in_field,  8, 2 );
$hour = substr( $in_field, 11, 2 );
$min = substr( $in_field, 14, 2 );
$sec = substr( $in_field, 17, 2 );
$t = mktime( $hour, $min, $sec, $month, $day, $year );

if ( $in_field == "0000-00-00 00:00:00" )
return "";
return (date($in_format, $t));


On 1/30/06, xkorakidis <[EMAIL PROTECTED]> wrote:
hi guys!
I'm trying to manage a table containing a timestamp colum
- when I insert a record, I don't fill a value in timestamp column, so
current timestamp is inserted. The inserted value is smth like
- when I try to show this value in a php page, I use
date($varOfTimestampColumn) or getdate(). The final result is the
highest possible date (smth like January 19 2038 I think)

How can I manage the timestamp field so as to show the correct value?

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 31/01/2006

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to