I'm trying to understand how you've set up the time fields. e.g. jforum_topics.topic_time is a varchar2(13). I can't figure out how to read it. I tried converting it to unix time, no luck. I looked at mysql time functions, no luck.
Is there a reason to do it this way ? Otherwise I volunteer to change it to timestamp instead of varchar(13) - or at least bigint.
To store it as varchar is very user unfriendly as it does not allow to look at the value with the normal database clients.
Using timestamps it would be very easy for an admin to find out at which time of day, at which day of week and so on his forum is receiving the most postings. This is important to know to properly schedule planed system downtimes for maintenance.
I have checked the database schema for jives, they are using varchar(15).
But already in the year 2002 they say :
Honestly, it's hard to even remember what databases were giving us issues with timestamps at this point
The very reason I am offering to change it now is because jforum is not yet released in release 1.0. Afterwards it is difficult to change anything at the database. I guess this is the reason jives is not changing anything, because they are in release 3.x and have thousands of running installations. [originally posted on jforum.net by marc]
:arrow: Shall be possible to migrate from other databases that uses unix time, like phpbb and jive, as well from current jforum installations.The migration script / program should be made by us. jforum-tools, at http://jforum-tools.dev.java.net is intended to host this helper projects. You already have Developer access to it.
:arrow: An easy way to use different date/time formats. For example, I have plans to make a XML driver using DBXML, so I should not have problems with that.
The migration will take use of the trick you mentionend. (jives link)
For postgres I have something like this in mind :
select ((post_time::text::bigint)/1000)::int::abstime::timestamp from jforum_posts;
I dont' think there should be problems with the string representation of the timestamps. I am only replacing the getLong/setLong with getTimestamp/setTimestamp. The rest will be done by the jdbc driver. [originally posted on jforum.net by marc]
I mean, "datetime or equivalent".. Oracle has a DATE field, as well postgresql and hsqldb ( well, afaik, hsqldb uses java.sql.Timestamp for datetime )..
Well... do the best.. just have in mind that, depending the size of the changes, it may be necessary to change another portion of codes in the system ( like the code that checks for unread forums and posts )
[originally posted on jforum.net by Rafael Steil]
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop