timestamp / int(10)

Discussion corner for Developers of Serendipity.
Post Reply
User avatar
gimmel
Regular
Posts: 118
Joined: Tue Jul 25, 2006 2:44 pm
Location: Quickborn/Germany
Contact:

timestamp / int(10)

Post by gimmel » Wed Apr 15, 2009 12:02 am

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

User avatar
kleinerChemiker
Regular
Posts: 765
Joined: Tue Oct 17, 2006 2:36 pm
Location: Vienna/Austria
Contact:

Re: timestamp / int(10)

Post by kleinerChemiker » Wed Apr 15, 2009 9:46 am

Timestamp exist since MySQL 4.1, maybe thats the reason?

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

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: timestamp / int(10)

Post by garvinhicking » Wed Apr 15, 2009 9:54 am

Hi!

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

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/


User avatar
gizmola
Regular
Posts: 37
Joined: Mon Oct 25, 2004 11:54 pm

Re: timestamp / int(10)

Post by gizmola » Wed May 20, 2009 9:17 am

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
Regular
Posts: 24
Joined: Thu Apr 16, 2009 7:05 am

Re: timestamp / int(10)

Post by Anson » Wed May 20, 2009 6:22 pm

gizmola wrote: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. :)

Post Reply