A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
Solution 1
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);
Solution 2
The equivalent of MS SQL Server or MySQL DATETIME data type or Oracle DATE data type is java.sql.Timestamp.
Solution 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.
Solution 4
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.
Related videos on Youtube
Stefan Hendriks
Software Developer. Coderetreat facilitator. Owner of Fundynamic. Ruby, Java, Javascript, C#, C, C++, HTML, CSS Author of: Dune 2 - The Maker / Dune 2 - The Maker 4J (github) RealBot (Counter-Strike 1.6 bot) (github) and much more... Of course all projects are hosted at Github.
Updated on July 08, 2022Comments
-
Stefan Hendriks almost 2 years
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.
-
Barak1731475 almost 11 yearsmysql documentation official mapping:dev.mysql.com/doc/refman/5.1/en/…
-
-
Stefan Hendriks almost 13 yearsthx. I am using MYSQL though, so I have updated the question.
-
Olaf almost 13 yearsUpdate answer to include MySQL.
-
Stefan Hendriks almost 13 yearsThanks, 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 almost 13 yearsI 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 about 11 yearsyou mentioned
java.sql.Date - A date only (no time)
then how comedate.getTime()
returns time? -
Bohemian about 11 years@FunsukWangadu because
java.sql.Date
is a subclass ofjava.util.Date
(as isjava.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 9 yearsplus one, especially for answering my follow up question about setting the preparedstatement!
-
ARK over 4 yearsHow do you retrieve a DateTime object in UTC (not in server's timezone) ?