What format is the time in?

[Migrated From Jforum.net]

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.

Help.
[originally posted on jforum.net by cbock]

[Migrated From Jforum.net]

It's the return of

System.currentTimeMillis();

You can use GregorianCalendar to get the formated date and time.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

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.

Edit :

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

http://www.jivesoftware.com/jive/thread.jspa?forumID=3&threadID=7771&messageID=58393#58393

If they didn't know in the year 2002 why they were doing this, I don't think we have a reason to do it two years later.
[originally posted on jforum.net by marc]

[Migrated From Jforum.net]

marc wrote: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.

Is much easy to work with, and does not address to implementation details of each database.

marc wrote:
To store it as varchar is very user unfriendly as it does not allow to look at the value with the normal database clients.

It can be easily workarounded using the same approach as in http://www.jivesoftware.com/jive/entry.jspa?entryID=211&categoryID=2

marc wrote:
If they didn't know in the year 2002 why they were doing this, I don't think we have a reason to do it two years later.

One thing I know for sure: we cannot change this until 1.0. After.. maybe.. but even now I cannot see a strong and good reason why we need to change.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

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]

[Migrated From Jforum.net]

3 points:

:arrow: DATETIME, not timestamp. Preferred format: yyyy-MM-dd hh:mm:ss ( 2004-10-03 22:10:33 )

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

Rafael


[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Datetime is a mysql (and mssql) thing. The sql (generic) way is timestamp :

http://troels.arvin.dk/db/rdbms/#data_types-date_and_time


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]

[Migrated From Jforum.net]

marc wrote:Datetime is a mysql (and mssql) thing. The sql (generic) way is timestamp :

http://troels.arvin.dk/db/rdbms/#data_types-date_and_time

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 )

Rafael


[originally posted on jforum.net by Rafael Steil]