SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL UNIX_TIMESTAMP() Colum

    lets say that you wanted to store a colum in mysql meant spefically for a mySQL timestamp()

    (if you put UNIX_TIMESTAMP() without any parameters, it will return the current timestamp in seconds)

    so...

    This could be stored in a CHAR(10) colum, or a INT(10) colum...

    which column would be a more wise choice...
    I can't believe I ate the whole thing

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    an integer is the way to go. but i suggest that you not use unix timestamps. almost all of the date and time functions in mysql are designed to use mysql's date, time and datetime columns, and not unix timestamps.

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well the reason why I am gonna use it, is meant only for sorting...

    I am going to have a php script that would list the most reciently active members...

    Before I had it as a TIMESTAMP() column (YYYYMMDDHHIIISS). However ever since mysql 4.1, the (YYYYMMDDHHIIISS) pattern has been deprecated. So now instead of having a DATETIME() column which would be updated as a session, I was thinking of putting the UNIX_TIMESTAMP as a column value.

    That way when the results are sorted as most active, then it sorts much faster... then compared to a 19 character length DATETIME column
    I can't believe I ate the whole thing

  4. #4
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by matsko
    well the reason why I am gonna use it, is meant only for sorting...

    I am going to have a php script that would list the most reciently active members...

    Before I had it as a TIMESTAMP() column (YYYYMMDDHHIIISS). However ever since mysql 4.1, the (YYYYMMDDHHIIISS) pattern has been deprecated. So now instead of having a DATETIME() column which would be updated as a session, I was thinking of putting the UNIX_TIMESTAMP as a column value.

    That way when the results are sorted as most active, then it sorts much faster... then compared to a 19 character length DATETIME column
    When you say "sorts" do you mean by most recent? If so then you can just perform an ORDER BY on the DATETIME column and get the most recent activity. MySQL will do the work for you.

  5. #5
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah I know, that's actually what is happening right now...

    it sorts the DATETIME column by DESC (therefore most recient), and yeah it works fine...

    However when I was using mysql 4.0 (when it had the TIMESTAMP column) it sorted them much faster...

    So that's why I looking into this UNIX_TIMESTAMP alternative...
    I can't believe I ate the whole thing

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by matsko
    This could be stored in a CHAR(10) colum, or a INT(10) colum...
    actually, the (10) in INT(10) has nothing to do with how big an integer you can store, or how many bytes it will take (all integers take only 4 bytes)

    what did you mean "the (YYYYMMDDHHIIISS) pattern has been deprecated"?

    http://dev.mysql.com/doc/refman/5.0/...stamp-4-1.html says:
    TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is YYYY-MM-DD HH:MM:SS.
    note very carefully that it says the display format

    internally, timestamps are stored in 4-byte fields, and should sort very, very efficiently (see http://dev.mysql.com/doc/refman/5.0/...uirements.html)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    rudy with the smackdown!

    datetime and timestamp columns are stored differently internally. if you switched from timestamp to datetime just because you didn't like the format, then yes, it is slower. the datetime format requires more bytes than timestamp, and therefore takes longer to sort.

  8. #8
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thank you, that's exactly what I needed to hear about the sorting...

    So in final would having a INT column to hold a UNIX_TIMESTAMP be similiar, in terms of sorting process...

    Because once again I am looking into the sorting...

    Incase you are wondering how I am going to use this feature, pay attention to my online members on the left of the page, (they are the most active members)

    http://www.tdotwire.com/
    I can't believe I ate the whole thing

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    unless i missed something, i think timestamp is the way to go since it updates itself automatically. but if you already have to touch that table manually everytime to keep that timestamp up to date, then a unix timestamp should be just fine, provided you never format the time.


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
  •