Formatting the MySQL Timestamp in php

[RT]

If anyone can help that would be great.

I¹m trying to format a timestamp from my MySQL table (sessions)

Here¹s the code I¹m using:

<?php echo date('D,n-j-y h:i:s a',strtotime($r ow_rsSessions['date'])); ?>
If I give the timestamp a value of 8 I can get the date to work correctly
but if it¹s at 10 or 12 I get the wrong date and it comes out the same for
every record * Mon,38-1-18-09:14:07 pm

Anyone have any thoughts ?

Thanks

[Pedro Graca]

RT wrote:

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

Please don't MIME us.
I¹m trying to format a timestamp from my MySQL table (sessions)

Here¹s the code I¹m using:

<?php echo date('D,n-j-y h:i:s a',strtotime($r ow_rsSessions['date'])); ?>
If I give the timestamp a value of 8
8? ?!?!?!?
I can get the date to work correctly
but if it¹s at 10 or 12
10?, 12? ?!?!?!?!
I get the wrong date and it comes out the same for
every record * Mon,38-1-18-09:14:07 pm
If I'm not mistaken that's "2038-01-19 02:14:07 UTC"
-- the limit for UNIX_TIMESTAMPs
Anyone have any thoughts ?
Let's see ...

~$ php -r 'echo date("Y-m-d H:i:s", strtotime("2004 1026163628")), "\n";'
1970-01-01 00:59:59

oops -- if you MySQL returns something like the above it will not work.
~$ php -r 'echo date("Y-m-d H:i:s", strtotime("2004-10-26 16:36:28")), "\n";'
2004-10-26 16:36:28

Aha! This works!
~$ php -r 'echo date("Y-m-d H:i:s", "20041026163628 "), "\n";'
2038-01-19 03:14:07

oops
~$ php -r 'echo date("Y-m-d H:i:s", "2004-10-26 16:36:28"), "\n";'
1970-01-01 00:33:24

oops
Let's now check MySQL ...
~$ mysql -upedro -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 350 to server version: 4.0.21-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select unix_timestamp( "20041026163828 ");
+----------------------------------+
| unix_timestamp( "20041026163828 ") |
+----------------------------------+
| 1098805108 |
+----------------------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
~$ php -r 'echo date("Y-m-d H:i:s", "1098805108 "), "\n";'
2004-10-26 16:38:28

Aha! Maybe it's best to select UNIX_TIMESTAMPs and let MySQL and PHP
worry about dates.

Thanks

<snip MIME stuff>

You're welcome.
--
USENET would be a better place if everybody read: | to mail me: simply |
http://www.catb.org/~esr/faqs/smart-questions.html | "reply" to this post, |
http://www.netmeister.org/news/learn2quote2.html | *NO* MIME, plain text |
http://www.expita.com/nomime.html | and *NO* attachments. |

[2metre]

RT wrote:

If anyone can help that would be great.

I¹m trying to format a timestamp from my MySQL table (sessions)

Here¹s the code I¹m using:

<?php echo date('D,n-j-y h:i:s a',strtotime($r ow_rsSessions['date'])); ?>
If I give the timestamp a value of 8 I can get the date to work correctly
but if it¹s at 10 or 12 I get the wrong date and it comes out the same for
every record * Mon,38-1-18-09:14:07 pm

Anyone have any thoughts ?

Thanks

What do you mean by 'give the timestamp a value of 8'?

If you feed the value 8 (or 10, or 12) as a timestamp for the date
function you should not get a sensible result. (Should be sometime on
Jan 1st 1970)

A timestamp starts at Jan 1st 1970 (roughly) and goes thru to 19th Jan
2038. Looks like your result doesn't come from the format string you
quoted but from 'D,y-j-n-h:i:s a' and that it is an adjustment back from
the max value for a timestamp.

Personally I would also reccommend using date formatting functions in
your SQL statement.

[Michael Fesser]

.oO(RT)

Please don't post HTML.

I¹m trying to format a timestamp from my MySQL table (sessions)
What type do you use in your database for storing the dates?
Here¹s the code I¹m using:

<?php echo date('D,n-j-y h:i:s a',strtotime($r ow_rsSessions['date'])); ?>

If I give the timestamp a value of 8 I can get the date to work correctly
but if it¹s at 10 or 12 I get the wrong date and it comes out the same for
every record * Mon,38-1-18-09:14:07 pm

Not sure what you mean, but you might want to have a look at MySQL's
date and time functions, especially DATE_FORMAT().

Micha

[neur0maniak]

There is a MySQL specific function called "FROM_UNIXTIME( )" which will
convert an epoch date number, to a standard SQL formatted date.

You're probably more comfortable with seeing dates formatted like that...
RT wrote:

If anyone can help that would be great.

I’m trying to format a timestamp from my MySQL table (sessions)

Here’s the code I’m using:

<?php echo date('D,n-j-y h:i:s a',strtotime($r ow_rsSessions['date'])); ?>
If I give the timestamp a value of 8 I can get the date to work
correctly but if it’s at 10 or 12 I get the wrong date and it comes out
the same for every record – Mon,38-1-18-09:14:07 pm

Anyone have any thoughts ?

Thanks