timestamp / int(10)

[gimmel]

I'm a little bit confused: I tried to make a statistic of my comments per month. So I wrote a MySQL query with a GROUP BY MONTH(timestamp) statement. It took me a long time to find out the problem: the time/date of an entry named „timestamp“ ist not stored in an MySQL timestamp as expected, it's stored in an int(10).

Is there a reason for storing timestamps in a non-timestamp format? (Except for saving some mktime commands?)

Is there a way to use the following code with timestamps stored int(10)?

Code: Select all

SELECT YEAR(timestamp) as year, MONTH(timestamp) as month, SUM(comments) as commentcount
FROM {$serendipity['dbPrefix']}entries
WHERE isdraft = 'false'
GROUP BY YEAR(timestamp), MONTH(timestamp)
ORDER BY timestamp ASC

[kleinerChemiker]

Timestamp exist since MySQL 4.1, maybe thats the reason?

You could use FROM_UNIXTIME() to convert the timestamp in your query.

[garvinhicking]

Hi!

int(11) is compatible with other databases, and simply uses UNIX timestamps, hence the naming.

Regards,
Garvin

[kleinerChemiker]

int(11)? Or do you mean int(10) unsigned?

[gizmola]

This is one of the most commonly confusing things about mysql. When specifying, let's say, an int using int(10) you are not getting any sort of precision. An int is an int is an int. All the (10) does is effect display in the mysql command line client tool.

The timestamp column is indeed unsigned (I checked), but as Garv correctly indicated, a mysql timestamp and int are roughly equivalent, both being unix timestamps under the covers. Depending on how you want to look at it, the decision to use an INT is either a nod to making serendipity database independent, or overlooking of the mysql feature.

Actually when serendipity started out, the mysql timestamp worked differently than it does now, so it's probably not a terrible thing that the timestamp specific format wasn't used.

[Anson]

Depending on how you want to look at it, the decision to use an INT is either a nod to making serendipity database independent, or overlooking of the mysql feature.

The ability to use PostgreSQL is the one feature that initially got me looking at s9y over WordPress, so I'd like to say again how glad I am that this is how it's done.

As for database-specific types... MySQL's timestamps are just Unix timestamps (seconds since the Unix epoch), but PostgreSQL timestamps are double-precision floating point distances from the year 2000, and SQL Server (that someone else was recently talking about porting s9y to) uses a completely different meaning of timestamp that can't really be used in this context... but the datetime type that could be used is a pair of integers: the number of days since 1/1/1900, and the number of milliseconds since midnight.

Using an int to store a Unix timestamp is way, way, way more reliable across databases than pretty much anything using the custom types.