Alex,

We discussed already earlier about database usage of unixtime or DATETIME.

Now, I looked into the Community comm_users and comm_sessions tables and found, that you use unix timestamp instead of DATEIME. As for comm_sessions table I would aggree to use unixtime.
But for comm_users table I don't not aggree. IMHO DATETIME would be better in that table.

In comm_users table, the comm_created and comm_lastlogin columns are unixtime based.
It means, that those possibilities to filter just by using an sql query LIKE '2003-12-10%' is impossible furthermore.
This possibility in Links SQL helps many times in filtering by a date. Now this is gone.
Additionally the possibility to use a database manager and check some info also gone, if you want to check any date right in the Community database...

I think, unixtime has not so much advantages of over DATETIME, to decide to use solely unixtime.
What are your reasons to use solely unixtime intead MySQL's internal DATETIME type?

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Hi,

The possibility is not gone. If you want to use an SQL query, use:

WHERE FROM_UNIXTIME(comm_created) LIKE '2003-12-10%'

The primary reason to move to int's are they are a lot easier to work with. Different databases treat dates very differently, and trying to accomodate for those differences is very hard.

Cheers,

Alex
--
Gossamer Threads Inc.

Eh, Alex, I thought that's why you created GT::SQL module and made it driver based using GT::SQL::Driver ... Supported databases of GT::SQL::Driver are: MySQL, Oracle, PostgreSQL, DB2, and MSSQL (through ODBC).
So what is the problem with normal dates like 2002-10-23 08:43:10? That's why the drivers are, to solve database incompatibilities.
Also for different date types GT::Date is also helpful. That's why (I suppose) it was created.

I also checked availability of FROM_UNIXTIME() function in different databases...
Function FROM_UNIXTIME | MySQL yes | Oracle no | PostgreSQL no | MSSQL no |
So? What is the advantage for us to use unixtime?

I also checked availability of DATETIME type in different databases...
Type DATETIME | MySQL yes | Oracle no (yes through DATE) | PostgreSQL yes | MSSQL yes |
Oracle also supports DATETIME, just by using the normal DATE type. This can be handled through Driver.

Conclusion:
1) All time related tasks we need, can be solved in ALL databases, using DATETIME type.
2) Your solution, to use unixtime, causes that we are able to execute SQL query on a date column only on MySQL, using an internal function FROM_UNIXTIME(). But all other databases will not able to execute query in that column, so will have to do within Perl, which slows down the full process...

Additionally, my LinksSQL plugins contains features, which contains filtering on DATE or DATETIME columns using LIKE. I will lose these features & lose performance, once the Community plugin may require to interact using unixtime.
BTW: using LIKE in WHERE clauses are suppoted by ALL mentioned databases.

I would like to kindly ask you to consider using DATETIME types in the comm_users table instead of unixtime, to be able to keep plugin compatibility and to be able to execute quick filterings using LIKE in queries, gaining performance with this.
Community app is still in Beta stage currently, so database change still can be done without too much problems. However after the first release that modification & upgrade would be much difficulter...

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Using a date versus a timestamp isn't to do with compatibility, it is an issue of flexibility. The fact of the matter is that timestamp are more flexible than date string. Period.

I'm not sure your research on the different database support for unix time is accurate either. I'd be astonished if oracle and msql didn't have a function to convert a timestamp to a date. In the rare occurance that there was nothing available you could simply select the timestamp and then use GT::Date or scalar localtime($timestamp)


"we" meaning "I" right?


As mentioned above, I'm fairly sure other database servers implement this but perhaps under a different function name. Just as a side point, using perl would slow down the process by an amount of time that is so small that an amoeba could fart and blow it away.

Paul, do it. You can research yourself. So go for it and search for a function what you think proves your statement. Here is my info source:
http://www.mysql.com/...rmation/crash-me.php
(BTW: that comparison was not done with the latest database versions, so may be possible that a feature was added in the meantime...)

If you decide to research yourself, don't forget you have to find unixtime to date function in all databases which don't support FROM_UNIXTIME() function directly, but may support through other function. So here are those databases, you should investigate for that function:
Oracle no | PostgreSQL no | MSSQL no

No, once you have to do comparisons or (even worser) date conversion for all (which may be more thousands) records, it becomes really time consuming. The more record, slower working, just because we can't use LIKE.
However when you use LIKE in a query, will filter quickly the result you need (executing by database, which is several times faster than do this in Perl).
IMHO no more explain is needed.

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Like I said, the function names will probably differ. You can't simply look along the FROM_UNIXTIME row and conclude that they don't support converting a timestamp to a date.


Hehe of course not. The mysql function will be quicker as it is implemented in C I expect but the way you are talking is like you think the perl version is hugely slower, which is just not true.

Have you done any benchmarking?


You should trust Alex's reasoning and answers, he has a lot of experience :)

Yes, I know. That's why I told you, you are free to do research on the subject.

I would expect at least 2x speed increase (even 4x or more), comparing Perl to C.

But if you want to benchmark it, do it then (please use example tables with 10.000, 50.000, 100.000, and 200.000 records). I would be curious for your results.

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

It is up to you to do the benchmark as you were making the claims :)

If we are talking about doing it in code, and not just for convienance, then I'd recommend doing:

my $start = GT::Date::timelocal(0,0,0,1,3,103);
my $end = GT::Date::timelocal(0,0,0,1,4,103);

select ... where comm_created > $start and comm_created < $end

and that will be much, much faster then if comm_created was a date field (integer comparisions in a database are way faster then date comparisions, especially if indexed).

If it's just for debugging in sql monitor, then use from_timestamp(), or whatever function your server supports.

Given the above, I don't see lack of LIKE '2003-03%' a significant reason to have all the headaches associated with handling date formats in a database.

Cheers,

Alex
--
Gossamer Threads Inc.

Ok. I accept that.

I would never want to work with dates in this format.

I wish you never regret, that you decided to use unixtime...

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

The speed difference you mention will probably favour unix times over DATETIMEs. A query such as:

... where column >= 1049516511 and column <= 1049600000

would probably be _faster_ than the LIKE query, especially with a proper index applied.

Additionally, we have decided to move towards the unix timestamps because they are completely time zone and time setting independant. Using a unix time means that someone can move their data to a server anywhere in the world, and the data contained within will not change. On the other hand, using a DATETIME has this problem - what does "2003-01-29 18:05:30" really mean? If I move my server tonight to the next time zone, and add another record tomorrow at exactly the same time, the time will now be stored as "2003-01-29 19:05:30". We could do it this way, or better yet, store in GMT, and use a time offset. But seriously, what is the point of that? You still can't do a 'COL LIKE "2003-01-29%"' query, because what you are probably looking for won't be returned due to the time (and therefore the start and end of the day) being offset.

A unix time, on the other hand, identifies a single and exact time. A time - 1043893026 - always represents a single second of time, and whether you are in North America, Europe, Australia, etc. it makes no difference. Here, in Vancouver, it is 'Wed Jan 29 18:17:06 2003', in GMT it is 'Thu Jan 30 02:17:06 2003', in New York it is 'Wed Jan 29 21:17:06 2003'. So the DATETIMEs differ, but the unix time does not - 1043893026 is 1043893026 in Vancouver, in New York, in GMT - in any time zone.

---

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com