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]

Re: Formatting the MySQL Timestamp in php

RT wrote:[color=blue][color=green]
>> This message is in MIME format. Since your mail reader does not understand[/color]
> this format, some or all of this message may not be legible.[/color]

Please don't MIME us.
[color=blue]
> 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[/color]

8? ?!?!?!?
[color=blue]
> I can get the date to work correctly
> but if it¹s at 10 or 12[/color]

10?, 12? ?!?!?!?!
[color=blue]
> I get the wrong date and it comes out the same for
> every record Mon,38-1-18-09:14:07 pm[/color]

If I'm not mistaken that's "2038-01-19 02:14:07 UTC"
-- the limit for UNIX_TIMESTAMPs
[color=blue]
> Anyone have any thoughts ?[/color]

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.

[color=blue]
> Thanks[/color]
<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]

Re: Formatting the MySQL Timestamp in php

RT wrote:[color=blue]
> 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
>
>[/color]
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]

Re: Formatting the MySQL Timestamp in php

.oO(RT)

Please don't post HTML.
[color=blue]
>I¹m trying to format a timestamp from my MySQL table (sessions)[/color]

What type do you use in your database for storing the dates?
[color=blue]
>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[/color]

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]

Re: Formatting the MySQL Timestamp in php

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:[color=blue]
> 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[/color]