MySQL UNIX_TIMESTAMP() Colum

[matsko]

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...

[longneck]

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.

[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

[Hartmann]

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.

[matsko]

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...

[r937]

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)

[longneck]

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.

[matsko]

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/

[longneck]

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.