SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Timestamp is not working properly!

    I created a TIMESTAMP column in one of my Table in MySQL. Normally, When I make any insert into that table that particular field (InsertTime) is supposed to hold the timestamp for that insert operation.

    When I query this

    SELECT InsertTime from hello
    It shows time in 'YYYY-MM-DD HH:MM:SS' format.

    2006-08-09 18:04:01
    Since I wanted timestamp in pure number(number of seconds elapsed since '1970-01-01), I did this.
    select InsertTime+0 from hello;
    The database threw up this:

    20060809180401
    Now when I tried to format this timestamp in php with this:

    PHP Code:
    print date('Y M j g i s'20060809180401); 
    it shows this:

    1938 Nov 6 11 59 45
    Now that is certainly no the time of insert( I don't think I was born then ). The system time in my OS is working perfectly. How did I screw the results?

    Does PHP and MySQL follow different timestamp patterns.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kailash Badu
    Does PHP and MySQL follow different timestamp patterns.
    most likely, yes

    i'll move this thread to the php forum, let's see what they say over there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try to use unix timestamp. It needs int column in db.
    To get current time use time() function.
    Then strftime() to format date. Very easy and comfortable.
    To format date according your locale, just setlocale().
    http://php-it.usefulclasses.com
    Blog on PHP and software selling

  4. #4
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Kailash Badu
    I created a TIMESTAMP column in one of my Table in MySQL. Normally, When I make any insert into that table that particular field (InsertTime) is supposed to hold the timestamp for that insert operation.

    When I query this



    It shows time in 'YYYY-MM-DD HH:MM:SS' format.



    Since I wanted timestamp in pure number(number of seconds elapsed since '1970-01-01), I did this.


    The database threw up this:



    Now when I tried to format this timestamp in php with this:

    PHP Code:
    print date('Y M j g i s'20060809180401); 
    it shows this:



    Now that is certainly no the time of insert( I don't think I was born then ). The system time in my OS is working perfectly. How did I screw the results?

    Does PHP and MySQL follow different timestamp patterns.
    use an int(10) for your timestamp column.

    Or when you get it from the db do this on it:

    strtotime('2006-08-09 18:04:01');

    or

    strtotime($row['timestamp']);

    You can also do date/time formatting right in the query if you need to:
    http://dev.mysql.com/doc/refman/5.0/...functions.html

  5. #5
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    shouldnt it be?

    Code:
    SELECT UNIX_TIMESTAMP(InsertTime) as timestamp from hello
    then you can feed $row['timestamp'] to php's date functions.


    because this:
    20060809180401

    is not a unix timestamp, that looks like a datetime string which had the spaces and the : removed from it.

  6. #6
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by clamcrusher
    is not a unix timestamp, that looks like a datetime string which had the spaces and the : removed from it.
    Heavens! That is it. And I thought it was a Unix Timestamp

    I wonder why my eyes didnít notice that. Thanks a millions.

    I will use the query you posted.

  7. #7
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by coo_t2
    Or when you get it from the db do this on it:

    strtotime('2006-08-09 18:04:01');

    or

    strtotime($row['timestamp']);

    You can also do date/time formatting right in the query if you need to:
    http://dev.mysql.com/doc/refman/5.0...-functions.html
    This too worked like a charm. Thank you.


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
  •