Home > Archive > PHP Programming > October 2004 > Formatting the MySQL Timestamp in php
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Formatting the MySQL Timestamp in php
|
|
|
| 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($row_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 2004-10-26, 3:56 pm |
| RT wrote:
> 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($row_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("20041026163628")), "\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 2004-10-26, 3:56 pm |
| 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($row_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 2004-10-26, 3:56 pm |
| .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($row_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
|
|
|
|
|