By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,445 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,445 IT Pros & Developers. It's quick & easy.

Formatting the MySQL Timestamp in php

P: n/a
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($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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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($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. |
Jul 17 '05 #2

P: n/a
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.
Jul 17 '05 #3

P: n/a
.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
Jul 17 '05 #4

P: n/a
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($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

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.