SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: MySQL TIMESTAMP

  1. #1
    SitePoint Addict
    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

  2. #2
    SitePoint Evangelist ckchin's Avatar
    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

  3. #3
    SitePoint Addict
    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.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    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;


  5. #5
    SitePoint Addict
    Join Date
    Apr 2002
    Location
    Miami
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    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

  6. #6
    Prolific Blogger silver trophy Technosailor's Avatar
    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
    Aaron Brazell
    Technosailor



  7. #7
    SitePoint Addict
    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.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    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>

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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

  10. #10
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •