473,326 Members | 2,095 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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($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
4 7050
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
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
.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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ben | last post by:
I would like to use php to query a database and retrieve a unix timestamp. The problem is that mysql is storing the data in the date format and not a timestamp. I am sure that I can amend my...
2
by: RT | last post by:
How would I format a timestamp which is used to show when a quote was created in a record set. This is the echo - <?php echo($row_rsquote); ?> I tried doing this - <?php echo date('D,...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
2
by: johndcal | last post by:
Hello All, I have a date value that I pull from a .csv file. After reading the file and storing the values in an array the value of the date could be found in $array, for example....
2
by: laredotornado | last post by:
Hello, Using PHP 4, MySQL 4, I am getting a date field $dbh = executeSQL($query); while ($row = mysql_fetch_array($dbh)) { $offer_id = $row; $dep_date = $row; // print out date print...
2
by: David | last post by:
Hi, Has anyone had this problem ? I am using MySQL ODBC 3.51 Driver, with MS Access 2003 and MySQL 4.1.11 standard log. I created my tables in MS Access, then exported them via ODBC to an...
6
by: Brandon | last post by:
I'm using PHP with MySQL 4.x and was having trouble converting a datetime from MySQL into a formatted string until I ran across this solution that converts a YYYY-MM-DD HH:MM:SS string into a...
3
by: mantrid | last post by:
Hello I have date and time in my mysql table in the form 2007-05-03 00:00:00 which I have dispayed on my webpage using echo $selldatetime I want to know how I can display it in the form...
10
by: WebCM | last post by:
There is a function: http://paste.ubuntu.com/21865 It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: datetime. If date is the same as today, the function returns "Today". There is one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.