• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

What format is the time in?

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's the return of



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

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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]
 
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
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic