Or.......
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]
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
Oh check this out!
--------------------------------------------------------------
a.. UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
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".
mysql> SELECT UNIX_TIMESTAMP();
-> 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.
Wicked?
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............
The MySQL manual's DATE AND TIME FUNCTIONS page..
;-) 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" <gdanen@xxxxxxxxx>
To: "xkorakidis" <xkorakidis@xxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Monday, January 30, 2006 8:34 AM
Subject: Re: 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 "";
else
return (date($in_format, $t));
}
Gerry
On 1/30/06, xkorakidis <xkorakidis@xxxxxxxx> 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
20060129213253
- 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?
Thanks!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Gerry
http://portal.danen.org/
--
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
