MySQL TIMESTAMP

[whiterabbit]

Can somebody give me a simple example of how exactly you are supposed to convert a TIMESTAMP output to readable date and time.

I have done a search here and found 3 pages including copying and pasting functions from some of the links provided and nothing is working.

I need to see what the code should look like in the query and then what it looks like as your taking it out of the array.

Thanks for your time, I cant beleive this is turning out to be such a difficult %$# task

jp

[ckchin]

Have you visited this link posted by DR_LaRRY_PEpPeR?

http://www.sitepointforums.com/showt...threadid=57952

[whiterabbit]

Hi

Yes I read this one. I am using a MySQL time stamp to keep track of a last updated record. The field increments automatically each time a query is made .

I just need to be able to take that value which I have now as unreadable and convert it to something more readable.

[freakysid]

Look at the mysql date and time functions:
http://www.mysql.com/doc/D/a/Date_an...functions.html

Especially, the DATE_FORMAT() function and the example given. Example:

SELECT DATE_FORMAT(myTimeStamp, '%W %M %Y')
FROM myTable;

[whiterabbit]

I read all that. It sounded like it did not apply to a TIMESTAMP. I think you lose the time basically if you do it that way.

I finally got it using this approach

My query statement:

$update = mysql_query("SELECT egroup, UNIX_TIMESTAMP(ts) as TS FROM lastchange WHERE ID= 2 ");

it wouldn't work unless I used as to create the alias TS which takes the place of (ts) in the fetch array statement:

while ($updates = mysql_fetch_array($update)) {
$grp = $updates["egroup"];
$dat = $updates["TS"];

and then embeded in some html in this way:

<?php echo
date("m/d/y h:i:s", $dat); ?>

I have 3 big books and none of them gave an example of converting the mysql TIMESTAMP to a Unix Timestamp . For a beginer like me, a sample of code used in context go's along way.

Anyway I just pasted all of this in case anyone has the same problem. It took me all day and now Im half blind

Thanks for the comment though I am going to try it that way as well

jp

[Technosailor]

can you do it in PHP instead of MySQL? that way you could use the mktime() function. It's prolly more efficient to do it through mySQL though.

Sketch

[whiterabbit]

What I learned is that yeah you could easily do it in php get the time and insert it into your database to use as time stamp but I wanted to make use of the MySQL TIMESTAMP specifically because it was designed for this type of application.

I hate to change my approach because I dont understand how something works. Unfortunately I lose alot of time that way,

In the end it's not difficult, like everything else when you know how it works...it aint hard.

[freakysid]

mysql> CREATE TABLE myTable ( myTimeStamp TIMESTAMP );
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO myTable VALUES (NOW());
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT DATE_FORMAT(myTimeStamp, '%W %M %Y') as formatedTimeStamp
    -> FROM myTable; 
+-------------------+
| formatedTimeStamp |
+-------------------+
| Sunday April 2002 |
+-------------------+
1 row in set (0.02 sec)

mysql>

[DR_LaRRY_PEpPeR]

exactly what i was gonna say.

yeah whiterabbit, DATE_FORMAT() should definitely work on a TIMESTAMP column. you don't have to convert it to a Unix timestamp and use PHP's date() function.

[whiterabbit]

Hi

Yes, I was getting confused with multiple posts. In the end I converted to a Unix Timestamp but it would have been easier as you say since I only need the date not the time.

Now if anyone knows of a good tutorial for importing a csv which contains only one field to my database let me know. Im now studying exactly how to open and parse a file and then write it into a database. So far all I have read is completely different form a normal insert statement.

cheers
jp