• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Date Issue Epoch time vs YYYY-MM-DD

 
Ranch Hand
Posts: 386
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have my getter and setters defined in the following manner:



And in the while loop, using JDBC I am retrieving my date like this :





I am returning the above date in a JSON response since this is a part of RESTful web service call. I am noticing one weird behavior.
When I run this webservice against my TEST database (Oracle), I get the date in EPOCH Unix time. However, when I run it against production database (Oracle),
I get it in the format of YYYY-MM-DD.

Could anyone tell me why it's happening like this? Does it have something to do with import java.sql.Date; that I am using?
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
An Epoch has nothing to do with operating systems or data representations.

In horological terms, an Epoch is a fixed reference point in time (and generally also in space). So the Unix Epoch has been fixed at midnight, January 1, 1970 at the Greenwich Observatory in England (which I believe aligns longitudinally with Paris France). The Unix internal datetime structures count time as an integral number of units from that point in space and time. Java uses Unix as its basis, so java.util.Date contains a signed long integer internally containing the number of milliseconds since that epoch.

There are at least 2 other commonly-used epochs. The most widely used one is, of course the Christian Era, dating from midnight UTC at the beginning of the Roman Year (?) January 1 of the ecclesiastically-defined birth of the Christian Messiah. The exact timing here has been adjusted multiple times throughout history and purists will note that the ecclesiastical birth year and the likely actual birth year differ by approximately 4 years.

The gold standard for epochs is probably the astronomical Julian Day. It is measured from noon Universal Time on January 1, 4713 BC. presumably since that's when the world was created, and if not, far enough back that most astronomical observations in recorded history are a positive number. Unlike other systems, there are no hours/minutes/seconds in Julian Days, just decimal fractions of a day.

Oracle is keeping date/time values in at least 2 different forms, depending on precision: day precision or seconds precision. The YYYY-MM-DD format is a display format unrelated to whatever internal storage format Oracle is using. And, it should be noted that YYYY-MM-DD is the preferred date display format for ALL SQL DBMSs. It doesn't have the confusion of the US-vs-world MM/DD/YY, DD/MM/YY, doesn't have Y2K issues, and collates nicely as text.

So in short, neither Java nor Oracle are keeping dates internally in a specific text format. That's just how they display them. Java, of course, has extensive date formatting services.

And finally, note that as I said initially, dates and times are best considered in space as well as time. The locale that you're in determines not only the preferred human format for date and time display, but also the timezone offset from the internal value. Some databases actually have different date/time data types to allow for local or UTC time values.
 
Jack Tauson
Ranch Hand
Posts: 386
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:An Epoch has nothing to do with operating systems or data representations.

In horological terms, an Epoch is a fixed reference point in time (and generally also in space). So the Unix Epoch has been fixed at midnight, January 1, 1970 at the Greenwich Observatory in England (which I believe aligns longitudinally with Paris France). The Unix internal datetime structures count time as an integral number of units from that point in space and time. Java uses Unix as its basis, so java.util.Date contains a signed long integer internally containing the number of milliseconds since that epoch.

There are at least 2 other commonly-used epochs. The most widely used one is, of course the Christian Era, dating from midnight UTC at the beginning of the Roman Year (?) January 1 of the ecclesiastically-defined birth of the Christian Messiah. The exact timing here has been adjusted multiple times throughout history and purists will note that the ecclesiastical birth year and the likely actual birth year differ by approximately 4 years.

The gold standard for epochs is probably the astronomical Julian Day. It is measured from noon Universal Time on January 1, 4713 BC. presumably since that's when the world was created, and if not, far enough back that most astronomical observations in recorded history are a positive number. Unlike other systems, there are no hours/minutes/seconds in Julian Days, just decimal fractions of a day.

Oracle is keeping date/time values in at least 2 different forms, depending on precision: day precision or seconds precision. The YYYY-MM-DD format is a display format unrelated to whatever internal storage format Oracle is using. And, it should be noted that YYYY-MM-DD is the preferred date display format for ALL SQL DBMSs. It doesn't have the confusion of the US-vs-world MM/DD/YY, DD/MM/YY, doesn't have Y2K issues, and collates nicely as text.

So in short, neither Java nor Oracle are keeping dates internally in a specific text format. That's just how they display them. Java, of course, has extensive date formatting services.

And finally, note that as I said initially, dates and times are best considered in space as well as time. The locale that you're in determines not only the preferred human format for date and time display, but also the timezone offset from the internal value. Some databases actually have different date/time data types to allow for local or UTC time values.




Thanks. So, what could be causing problems in my case?
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There should be no difference between one database and another unless one of them has defined the date as a text database data type instead of as a date/time data type.

However, I'd expect that JSON would probably default to formatting date/time values in Unix form (Actually, I think there's an ISO standard that defines "Unix" date/time text formatting). SQL*PLUS, on the other hand, I'd expect to show dates and times in SQL format, even though the actual data in the database was the same for both applications.
 
Jack Tauson
Ranch Hand
Posts: 386
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:There should be no difference between one database and another unless one of them has defined the date as a text database data type instead of as a date/time data type.

However, I'd expect that JSON would probably default to formatting date/time values in Unix form (Actually, I think there's an ISO standard that defines "Unix" date/time text formatting). SQL*PLUS, on the other hand, I'd expect to show dates and times in SQL format, even though the actual data in the database was the same for both applications.



Hmm. The data type is same in both the database, which is DATE.

So, is there a way I can control the date format and display in YYYY-MM-DD format? Because the date changed to Unix time, some of the things in UI are breaking for me. It works fine in production since I am still getting it in the format YYYY-MM-DD. Thanks
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What date value are you referring to exactly?
The values in the classes in the OP are all Dates, and they have no format at all.  They're just a long, essentially.

So if there's an issue with how that value is being displayed somewhere then that is where the issue lies, and is nothing to do either with Oracle or JDBC.
 
Jack Tauson
Ranch Hand
Posts: 386
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:What date value are you referring to exactly?
The values in the classes in the OP are all Dates, and they have no format at all.  They're just a long, essentially.

So if there's an issue with how that value is being displayed somewhere then that is where the issue lies, and is nothing to do either with Oracle or JDBC.




In both the databases, the dates have this value : 11/09/2016 08:10:00 PM

Webservice call(JSON Response) in TEST returns Unix time which is messing my UI code and in production it returns it in YYYY-MM-DD format which is fine with my UI. UI and Java code is same for both TEST and Production servers. So, I was wondering what's causing this issue.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:What date value are you referring to exactly?
The values in the classes in the OP are all Dates, and they have no format at all.  They're just a long, essentially.

So if there's an issue with how that value is being displayed somewhere then that is where the issue lies, and is nothing to do either with Oracle or JDBC.

 
Marshal
Posts: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:. . . Greenwich Observatory . . .  aligns longitudinally with Paris France . . . .

No, according to Wikipedia Paris is 2° 21″ east of Greenwich.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jack Tauson wrote:
In both the databases, the dates have this value : 11/09/2016 08:10:00 PM

Webservice call(JSON Response) in TEST returns Unix time which is messing my UI code and in production it returns it in YYYY-MM-DD format which is fine with my UI. UI and Java code is same for both TEST and Production servers. So, I was wondering what's causing this issue.



OK, so my point still stands.
It's not the bit of code you just posted that is the problem.
It is whatever is turning the Date into a String for the Json response.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:
It is whatever is turning the Date into a String for the Json response.



And it is perfectly reasonable for the default format for a JSON date to be ISO 8601, since that's the format that web clients normally deal with and JSON was designed originally for web clients.

More info: https://weblog.west-wind.com/posts/2014/Jan/06/JavaScript-JSON-Date-Parsing-and-real-Dates
 
Jack Tauson
Ranch Hand
Posts: 386
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:

Jack Tauson wrote:
In both the databases, the dates have this value : 11/09/2016 08:10:00 PM

Webservice call(JSON Response) in TEST returns Unix time which is messing my UI code and in production it returns it in YYYY-MM-DD format which is fine with my UI. UI and Java code is same for both TEST and Production servers. So, I was wondering what's causing this issue.



OK, so my point still stands.
It's not the bit of code you just posted that is the problem.
It is whatever is turning the Date into a String for the Json response.



The reason I am confused is because that thing which is converting Date into String for Json response is same for TEST and Production Code.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jack Tauson wrote:In both the databases, the dates have this value : 11/09/2016 08:10:00 PM



In database terminology, that is not a DATE. That is a TIMESTAMP. Unfortunately the java.util.Date class represents a TIMESTAMP rather than a DATE.

And also unfortunately, the ResultSet getDate() method returns a java.sql.Date object, which represents a DATE but which unfortunately can be cast to java.util.Date.

It's not clear which Date class your posted code uses, but because you used rs.getDate() you are going to get only the DATE part of your database's TIMESTAMP value. Which it sounds like that's what you want. However once you get that, it's your responsibility to format it in the way that it should appear as text. It looks like you're allowing something else to format the date and that something differs between your two systems, and that's why you should do the date-formatting yourself.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:

Jack Tauson wrote:In both the databases, the dates have this value : 11/09/2016 08:10:00 PM



In database terminology, that is not a DATE. That is a TIMESTAMP. Unfortunately the java.util.Date class represents a TIMESTAMP rather than a DATE.



And even more unfortunately, that's not how Oracle defines things: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT413

SQL type TIMESTAMP was added to the SQL standard long after Oracle had already set things up in their own quirky way.

Nevertheless, as everyone - including myself - has said, the internal format isn't the issue. Regardless of what's in the "DATE" object and how precise it is, the actual conversion of the date to a text (string) representation is external to the database and is determined by the mechanism that's doing the formatting.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jack Tauson wrote:
The reason I am confused is because that thing which is converting Date into String for Json response is same for TEST and Production Code.



I can pretty much guarantee it isn't.

Accidentally changing the serialiser used by something like Gson is quite easy.
I've just come off dealing with a bug caused by exactly that two days ago, and it also changed the format of a timestamp.

You'll need to poke around that area and see exactly how that is all wired up.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic