SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: MySQL TIMESTAMP
-
Apr 27, 2002, 17:48 #1
- Join Date
- Apr 2002
- Location
- Miami
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
MySQL TIMESTAMP
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
-
Apr 27, 2002, 17:56 #2
- Join Date
- Mar 2002
- Location
- msia
- Posts
- 487
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Have you visited this link posted by DR_LaRRY_PEpPeR?
http://www.sitepointforums.com/showt...threadid=57952
-
Apr 27, 2002, 18:07 #3
- Join Date
- Apr 2002
- Location
- Miami
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Apr 27, 2002, 22:24 #4
- Join Date
- Jun 2000
- Location
- Sydney, Australia
- Posts
- 3,798
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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;
-
Apr 27, 2002, 22:37 #5
- Join Date
- Apr 2002
- Location
- Miami
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Apr 27, 2002, 22:44 #6
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Apr 27, 2002, 22:52 #7
- Join Date
- Apr 2002
- Location
- Miami
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Apr 28, 2002, 00:28 #8
- Join Date
- Jun 2000
- Location
- Sydney, Australia
- Posts
- 3,798
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code: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>
-
Apr 28, 2002, 15:53 #9
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
Apr 28, 2002, 16:20 #10
- Join Date
- Apr 2002
- Location
- Miami
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks