year to date conversion

[raj]

hi ,

i have a string sYear = 2008;
i need search from the database the results which are in the year
2008.

so how can i convert 2008 to this format

sDateFrom = "2008-01-01";
sDateTo = "2008-12-31";

thanks in advance...
raj

 

[Lothar Kimmeringer]

so how can i convert 2008 to this format

sDateFrom = "2008-01-01";
sDateTo = "2008-12-31";

For that specific thing you can use
Calendar.set(2008, Calendar.YEAR)
etc. and java.sql.Date:

new java.sql.Date(calendar.getTime()).toString()


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Stefan Rybacki]

...
Or, wait, how about this - use the database query language itself to
retrieve the desired records? Genius!
Agreed!


In Postgres, that'd be:

SELECT columns FROM footable WHERE EXTRACT( YEAR FROM dcol ) = 2008;

In MySQL it would be something like

SELECT columns FROM footable WHERE YEAR(dcol) = 2008

 

[Lothar Kimmeringer]

so how can i [sic] convert 2008 to this format

sDateFrom = "2008-01-01";
sDateTo = "2008-12-31";

For that specific thing you can use
Calendar.set(2008, Calendar.YEAR)
etc. and java.sql.Date:

new java.sql.Date(calendar.getTime()).toString()

The 'toString()' approach is just goofy, ...

The toString() of java.sql.Date is not goofy, because it returns
the date in ISO-format, i.e. exactly in the format the OP requested.
No need to use DateFormat or something else. Of course you would
be able to use PreparedStatement.setDate(col, sqlDate), but that's
a different story ;-)

given that the OP said:

i [sic] need search from the database the results which are in the year
2008.

Without knowing the database, you can't assume that there is a
"EXTRACT( YEAR FROM dcol ) = 2008" possible and if you want
to be database-independent you have to build a query like
"where date >= ? and date <= ?" and set the parameter in your
PreparedStatement anyway, so the most general way is using
java.sql.Date and setDate(...) as shown above or - if you don't
want to use PreparedStatements use the toString-method of
java.sql.Date as shown in my previous post.

It's especially goofy to build a 'Calendar', which solves the problem, then
convert to a 'String' to try to solve the problem.

The string is created by java.sql.Date, the Calendar is used to
be able to set days and months directly.

It's even more especially goofy to convert a 'Calendar' to a 'Date' in order
to convert to a 'String'.

Again, it's java.sql.Date and not java.util.Date.

And then to use 'toString()' instead of a 'DateFormat' - that takes goofy to a
whole new level.

Did I mention that it's java.sql.Date we're talking about? ;-)

Why not just compare 'Calendar.get( Calendar.YEAR )' from the result to that
of the query term?

Or, wait, how about this - use the database query language itself to retrieve
the desired records? Genius!

In Postgres, that'd be:

SELECT columns FROM footable WHERE EXTRACT( YEAR FROM dcol ) = 2008;

So you only need to parse the JDBC-driver's vendor-information to
find out what database you're using to create the fitting
SQL-Statement? Genius!


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Lew]

so how can i [sic] convert 2008 to this format
   sDateFrom = "2008-01-01";
   sDateTo = "2008-12-31";

The 'toString()' approach is just goofy, ...

The toString() of java.sql.Date is not goofy, because it returns
the date in ISO-format, i.e. exactly in the format the OP requested.
No need to use DateFormat or something else. Of course you would
be able to use PreparedStatement.setDate(col, sqlDate), but that's
a different story ;-)

That is *the* story.

And the OP said he needed to determine if a date was from a given
year, not that he needed to format it in ISO format. I saw nothing in
his post that mandated ISO format as such; in fact, the reference to
using Strings was his focus on a suboptimal implementation of an
overarching goal to extract only records from a particular year.

given that the OP said:

i [sic] need search from the database the results which are in the year
2008.

Without knowing the database, you can't assume that there is a
"EXTRACT( YEAR FROM dcol ) = 2008" possible and if you want

Other than the fact that it *is* the SQL standard.

One can, of course, know which database they are using and just use
the syntax for that RDBMS. It is very rare that an application needs
to support more than one DMBS.

to be database-independent you have to build a query like

It is rare to need to be database independent.

"where date >= ? and date <= ?" and set the parameter in your
PreparedStatement anyway, so the most general way is using
java.sql.Date and setDate(...) as shown above or - if you don't
want to use PreparedStatements use the toString-method of
java.sql.Date as shown in my previous post.

The latter being an antipattern. One should always use
PreparedStatements.

The string is created by java.sql.Date, the Calendar is used to
be able to set days and months directly.
Goofy.


Again, it's java.sql.Date and not java.util.Date.

And that matters because ...?

It's still goofy, due to the conversion to a 'String'.

Did I mention that it's java.sql.Date we're talking about? ;-)

Did I ask why that matters?

So you only need to parse the JDBC-driver's vendor-information to
find out what database you're using to create the fitting
SQL-Statement? Genius!

Indeed it is.

On every database-related project I have ever worked on in ten years
of professional Java programming for many clients, I have *always*
known what database would be deployed.

Hardly an egregious condition.

--
Lew

"Always remember: The answer is forty-two, there can only be wrong questions!"

The /Hitchiker's Guide/ series is banal, boring and bad literature.
Philosophy based on it is especially dorky. Everyone knows the real
magic number is 23.

 

[Arne Vajhøj]

Indeed it is.

On every database-related project I have ever worked on in ten years
of professional Java programming for many clients, I have *always*
known what database would be deployed.

You have never worked on a project with a requirement to be
database independent ?

Arne

 

[Arne Vajhøj]

So you only need to parse the JDBC-driver's vendor-information to
find out what database you're using to create the fitting
SQL-Statement? Genius!

That would be very bad code.

DatabaseMetaData actually has methods to tell about features
of the SQL dialect.

Arne

 

[Lew]

You have never worked on a project with a requirement to be
database independent ?

Correct, in my Java programming experience.

I have worked on projects that supported two RDBMSes, and I routinely
test my work on at least one DBMS other than the official one for a
project, but none of the Java projects have had a requirement for
database independence. Worst case was a requirement to support two
DBMSes, and they were selected ahead of time. This for clients both
large and small, government and private industry, with various
application servers and back ends, for just shy of ten years now.

My experience might not be typical.

It should help for this discussion that 'EXTRACT' is a Standard SQL
function. A "SQL" DBMS that doesn't support it is not standard-
compliant.

 

[Arne Vajhøj]

raj wrote:
so how can i [sic] convert 2008 to this format
sDateFrom = "2008-01-01";
sDateTo = "2008-12-31";
Lothar Kimmeringer wrote:

Without knowing the database, you can't assume that there is a
"EXTRACT( YEAR FROM dcol ) = 2008"

For portability, you could use:

{fn YEAR(dcol)} = 2008

Oh.

Someone has actually read the JDBC docs !

Arne

 

[Arne Vajhøj]

Correct, in my Java programming experience.

I have worked on projects that supported two RDBMSes, and I routinely
test my work on at least one DBMS other than the official one for a
project, but none of the Java projects have had a requirement for
database independence. Worst case was a requirement to support two
DBMSes, and they were selected ahead of time. This for clients both
large and small, government and private industry, with various
application servers and back ends, for just shy of ten years now.

My experience might not be typical.

It is a requirement that happen.

It is frequently seen in product development but is rare
in project development.

It should help for this discussion that 'EXTRACT' is a Standard SQL
function. A "SQL" DBMS that doesn't support it is not standard-
compliant.

Maybe.

But not all commonly used databases have it.

SQLServer and MySQL does not have it.

Arne

 

[Arne Vajhøj]

Lew wrote:
raj wrote:
so how can i [sic] convert 2008 to this format
sDateFrom = "2008-01-01";
sDateTo = "2008-12-31";
Lothar Kimmeringer wrote:
Without knowing the database, you can't assume that there is a
"EXTRACT( YEAR FROM dcol ) = 2008"

For portability, you could use:

{fn YEAR(dcol)} = 2008

Oh.

Someone has actually read the JDBC docs !

It should be noted though that JDBC does not guarantee
that YEAR is implemented, but it can be checked in
DatabaseMetaData.

Arne

 

[Lothar Kimmeringer]

That is *the* story.

And the OP said he needed to determine if a date was from a given
year, not that he needed to format it in ISO format.

Read it again. There were two questions. First the one you said,
the second one how to get dates in ISO-formats:

| so how can i convert 2008 to this format
|
| sDateFrom = "2008-01-01";
| sDateTo = "2008-12-31";

I saw nothing in
his post that mandated ISO format as such; in fact, the reference to
using Strings was his focus on a suboptimal implementation of an
overarching goal to extract only records from a particular year.

I was focused on the second answer, because even with Prepared-
Statements you need Calendar and java.sql.Date.

given that the OP said:
i [sic] need search from the database the results which are in the year
2008.

Without knowing the database, you can't assume that there is a
"EXTRACT( YEAR FROM dcol ) = 2008" possible and if you want

Other than the fact that it *is* the SQL standard.

So? Depite the fact, that you have to assume that above statement
might not work with all databases (I don't take my time to prove
that by trying them all out right now), there is another difference
between the DB-independent solution and yours:

----- snip
explain select * from dw_log where EXTRACT( YEAR FROM date_as_ts ) = 2009

table: dw_log
type: ALL
possible_keys: null
key: null
key-len: null
ref: null
rows: 7911
Extra: Using where
----- snip
explain select * from dw_log where date_as_ts >= '2009-01-01' and date_as_ts <= '2009_12-31' limit 10

table: dw_log
type: range
possible_keys: dateindex_1
key: dateindex_1
key-len: 9
ref: null
rows: 1
Extra: Using where
----- snip

See the difference? At least this is the case for MySQL. Maybe
PostgreSQL behaves differently (they know interesting stuff
like Conditional Indizes, so I can't say), but tables with more
than - let's say - 10,000,000 entries and you will run into problems
with extract(year).

So looking at your suggestions how to solve that, I have to assume
that the tables of your applications never grow that big to let
you see the impact on performance with these kind of statements.

One can, of course, know which database they are using and just use
the syntax for that RDBMS. It is very rare that an application needs
to support more than one DMBS.

It's rare in your area but don't talk for others here, please.

It is rare to need to be database independent.

No. As soon as you had a migration from one database to another
once, you will see that database-independent programming has
its benefits, even if the conditions were clear at the beginning
of the project.

Changing the database is quite common, it happened just last month
with one of our customers (from MaxDB to Oracle).

The latter being an antipattern. One should always use
PreparedStatements.

Sure, but again, there are moments, where a PreparedStatement
is not needed. Without knowing the specific parameters, you
can't deny the usage.

Goofy.

Not at all. How do you create a Parameter for a PreparedStatement
that is used for a column of type date(time) with a specific date?
java.sql.Date.valueOf? Always? Goofy.

And that matters because ...?

It's still goofy, due to the conversion to a 'String'.

If you want a String in ISO-format you have to convert it to a String.
java.sql.Date.toString() is the most uncomplicated way without
the need of using SimpleDateFormat etc.

Indeed it is.

Sure. I'm actually doing this, e.g. to be able to return null
when trying to read in datetimes from MySQL in combination with
some versions of the JDBC-driver. Instead of returning null
itself, it tries to parse 0000-00-00 00:00:00.0000 as timestamp
leading to an exception.

It's ugly, ugly, ugly and should be avoided at all costs.

On every database-related project I have ever worked on in ten years
of professional Java programming for many clients,

I don't want to question your professionalism (how could I, I don't
know you), but: A man smoking for 20 years is not a professional
smoker, but an idiot with 20 years of experience. In other words:
I think that people using the time they already work with something
are simply running out of arguments.

I have *always*
known what database would be deployed.

So you're lucky and admit that you don't have *any* experience with
the development of database-independent applications. I do, so you
might just believe some of the points above. The most annoing thing
that can happen is a database-migration where you have to rewrite
more or less every statement you use, starting with easy things
like the replacement of "!=" with "<>" and really hard stuff like
recreating the logic behind algorithms that create SQL-statements
dynamically.

Hardly an egregious condition.

It is, you're just lucky.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Lothar Kimmeringer]

SQLServer and MySQL does not have it.

MySQL does, MS SQL I don't know.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Lothar Kimmeringer]

That would be very bad code.

The statement was meant sarcastically in the same way as it
was meant in the text I was quoting.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

 

[Lothar Kimmeringer]

The difference is that you declared the wrong index in the first case, then
tried to claim that the lack of an index is the fault of the idiom rather than
of the programmer.

OK, I'm not using "extract(year)" in my statements because it
can be solved with the range as being shown in the second
statement. So that means that you need to create at least
two indizes for doing one thing.

The fault in the second case is that you did string comparisons. Do date (or
timestamp) comparisons if the column is of DATE (TIMESTAMP) type.

There is nothing wrong with the second statement but the syntax
you have to use with a MySQL-query-monitor to specify a date. The
explain-result clearly shows that the DB-engine is using an index
rather than doing a full table scan.

When using JDBC and PreparedStatement you of course use the Date-
class but I'm not starting to program classes just to perform
an explain-statement on MySQL.

I'll talk for whomever I like.

You shouldn't. You might express your opinion that your point of
view is shared by others but don't assume that everybody is with you.

And such changes require rewrites of SQL statements. Life is tough, SQL isn't
compatible, change requires change.

The last time I had to change statements due to a change of the
underlying database I can't say for sure, because I really
don't remember. Must be six or seven years ago.

I have always used PreparedStatement. It can do everything regular Statement
can do, being a subclass, and it can accept parameters in a type-guaranteed
and injection-proof way. You have not presented a reason not to use it.

I don't need to but e.g. a SQL-Monitor allowing users to enter
SQL-Statements directly or a class readin in SQL-statements from
a file and execute them on the database. Nothing that you come
along very often, but nothing unlikely, either.

If you say so. Seems normal to create a java.sql.Date (or Timestamp) for a
parameter representing a date (timestamp). You don't think so?

The intersting part is how you set a specific date; you were expressing
your dislike of my way using Calendar and java.sql.Date.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!