• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Why date/datetime datatype instead of long or varchar2/string

 
Ranch Hand
Posts: 133
1
jQuery Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a question, can we use long or string/varchar2 datatypes to store the date instead of date/datetime itself?
The reason is like date object itself is heavy while sorting, applying index etc i.e. having issues when you need some performance.

So if we store the date in terms of long or in string format like  e.g."yyyyMMddhhmmss" etc then will it help to interns of sort or applying indexing and even helps to cnvert the value into required date format.
Is this assumption is right or wrong?

Thanks,
Atul
 
Saloon Keeper
Posts: 6163
155
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

The reason is like date object itself is heavy while sorting, applying index etc i.e. having issues when you need some performance.


Please elaborate what you mean by this, and why you think that; it sounds wrong.
 
Atul More
Ranch Hand
Posts: 133
1
jQuery Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thats what I need to understand why it is wrong?
The reason is, I read couple of sites that avoid to use indexes on date or datetome column.
I have to deal with the huge data and it has date column which is very important to me.
So thats the reason I came with this thought and try to understand its pros and cons.
 
Saloon Keeper
Posts: 21710
148
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DateTime/Timestamp objects both in Java and in most databases are stored internally as binary values. The actual conversion of such objects to month/day/year calendar format (and hour/minutes/seconds) is done by the support infrastructure - the SQL parser and display services in databases and the member functions in the various calendar-related Java classes. So there's no "heavy" to it.

In fact, the reason for the Unix joke that "the world ends in 2038" comes from the fact that Unix dates are held as 64-bit integers, and given that The Dawn of Time is January 1, 1970 UTC in Unix, that's how many seconds it takes to use up all 64 bits worth of values. At this moment, we're are 1582289210.

Be VERY careful when using dates as database indexes and AVOID using them as primary keys. Like floating-point numbers, they are imprecise values and subject to having their edges shaved off. Oracle, for example has a date granularity in seconds or microseconds, but Java's java.sql.Date and java.util.Date are granular to milliseconds. Trying to do a "key=value" search when such shenanigans are in play is a recipe for trouble.
 
Sheriff
Posts: 6801
182
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim, maybe the OP could have a DateTime and DateTimeSort columns, the latter would be a string in "yyyyMMddhhmmss" format.  I can see tagging milliseconds on to that string too. Would that work?
 
Tim Holloway
Saloon Keeper
Posts: 21710
148
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Tim, maybe the OP could have a DateTime and DateTimeSort columns, the latter would be a string in "yyyyMMddhhmmss" format.  I can see tagging milliseconds on to that string too. Would that work?



Well, as I said, internally the format is binary, so the external string format is moot - and for that matter, dependent on how the retrieving application wished to format it. Internally, days, hh:mm:ss: and millii-microseconds are just part of one big number and any sort on un-natural orders for data retrieval would be up to the sorting algorithm used, not the internal database code.
 
Look! I laid an egg! Why does it smell like that? Tiny ad, does this smell weird to you?
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!