A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)

Question

So far, I have not found a clear answer to this.

I'd like to know what the equivalent is for a SQL type DATETIME and the java type, using a PreparedStatement.

I have found: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm

But it states that SQL type "DATETIME" is the same as sql.date, but when looking at the SQL date docs (http://download.oracle.com/javase/7/docs/api/java/sql/Date.html), it says the time is truncated (all zeros).

What I want is to be able to specify a preparedStatement.setDateTime() or some sort.

The only other way I see is using a timestamp, but that would require me to change the column type, while I cannot imagine someone else never had this problem before?

Any hints?

Edit: I am using MYSQL.

mysql documentation official mapping:dev.mysql.com/doc/refman/5.1/en/… — Barak1731475
– Barak1731475, Commented Jul 8, 2013 at 13:26

Bohemian · Accepted Answer · 2014-05-11 23:17:20Z

148

The java.sql package has three date/time types:

java.sql.Date - A date only (no time part)
java.sql.Time - A time only (no date part)
java.sql.Timestamp - Both date and time

You want the last one: java.sql.Timestamp.

If you are using these types, you don't need to call a specific setter; just use:

java.util.Date date = new Date();
Object param = new java.sql.Timestamp(date.getTime());
// The JDBC driver knows what to do with a java.sql type:
preparedStatement.setObject(param);

edited May 11, 2014 at 23:17

answered Jul 21, 2011 at 14:44

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stefan Hendriks Over a year ago

Thanks, this is what I was looking for. How did you find out that these types match with certain SQL types? I am a bit troubled, since my query works fine with using NOW(), but not using a date instance of "now" in java and use it as a timestamp?

Stefan Hendriks Over a year ago

I found out that I had made a mistake in the query. I missed something obvious. Your answer is the solution, thanks a bunch! :)

Java Spring Coder Over a year ago

you mentioned java.sql.Date - A date only (no time) then how come date.getTime() returns time?

Bohemian Over a year ago

@FunsukWangadu because java.sql.Date is a subclass of java.util.Date (as is java.sql.Time), so even though it isn't a "moment in time", it inherits all the methods as if it were. It's a design flaw in the java API.

jumps4fun Over a year ago

plus one, especially for answering my follow up question about setting the preparedstatement!

Olaf · Accepted Answer · 2011-07-21 14:38:41Z

7

The equivalent of MS SQL Server or MySQL DATETIME data type or Oracle DATE data type is java.sql.Timestamp.

answered Jul 21, 2011 at 14:38

Olaf

6,2991 gold badge21 silver badges38 bronze badges

2 Comments

Stefan Hendriks Over a year ago

thx. I am using MYSQL though, so I have updated the question.

Olaf Over a year ago

Update answer to include MySQL.

everag · Accepted Answer · 2011-07-21 17:08:59Z

3

In Java we have java.util.Date to handle both Date and Time values.

In SQL, you have commonly Dates (only dates), Time (only time) and DateTime/Timestamp (date and time).

In your Java program, usually you'll always have java.util.Date, so each time you're setting Dates/Times/DateTimes in PreparedStatements, always choose exactly which one you need, according to the database.

answered Jul 21, 2011 at 17:08

everag

7,6822 gold badges32 silver badges45 bronze badges

1 Comment

ARK Over a year ago

How do you retrieve a DateTime object in UTC (not in server's timezone) ?

Cristiano Fontes · Accepted Answer · 2011-07-21 14:46:46Z

1

I had a similar problem with my Mysql having SQL date and locally in my app i only had Date

I solved like this

java.sql.Date dataStartSql = new java.sql.Date(start.getTime());

After that used the setDate normally, and I used a getTimestamp to retrive the first value.

where start is a Date object.

answered Jul 21, 2011 at 14:46

Cristiano Fontes

5,0886 gold badges46 silver badges77 bronze badges

Collectives™ on Stack Overflow

A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related