Sql date with time java

Class Date

A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT.

To conform with the definition of SQL DATE , the millisecond values wrapped by a java.sql.Date instance must be ‘normalized’ by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Constructor Summary

Method Summary

This method always throws an UnsupportedOperationException and should not be used because SQL Date values do not have a time component.

Obtains an instance of Date from a LocalDate object with the same year, month and day of month value as the given LocalDate .

Methods declared in class java.util.Date

Methods declared in class java.lang.Object

Constructor Details

Date

Constructs a Date object initialized with the given year, month, and day. The result is undefined if a given argument is out of bounds.

Date

Constructs a Date object using the given milliseconds time value. If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

Method Details

setTime

Sets an existing Date object using the given milliseconds time value. If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

valueOf

toString

getHours

This method is deprecated and should not be used because SQL Date values do not have a time component.

getMinutes

This method is deprecated and should not be used because SQL Date values do not have a time component.

getSeconds

This method is deprecated and should not be used because SQL Date values do not have a time component.

setHours

This method is deprecated and should not be used because SQL Date values do not have a time component.

setMinutes

This method is deprecated and should not be used because SQL Date values do not have a time component.

setSeconds

This method is deprecated and should not be used because SQL Date values do not have a time component.

valueOf

Obtains an instance of Date from a LocalDate object with the same year, month and day of month value as the given LocalDate . The provided LocalDate is interpreted as the local date in the local time zone.

toLocalDate

toInstant

This method always throws an UnsupportedOperationException and should not be used because SQL Date values do not have a time component.

Report a bug or suggest an enhancement
For further API reference and developer documentation see the Java SE Documentation, which contains more detailed, developer-targeted descriptions with conceptual overviews, definitions of terms, workarounds, and working code examples. Other versions.
Java is a trademark or registered trademark of Oracle and/or its affiliates in the US and other countries.
Copyright © 1993, 2023, Oracle and/or its affiliates, 500 Oracle Parkway, Redwood Shores, CA 94065 USA.
All rights reserved. Use is subject to license terms and the documentation redistribution policy.

Источник

java.sql.Date, java.sql.Time, and java.sql.Timestamp

Most of database support three forms of datetime fields which are DATE, TIME and TIMESTAMP. Each of these have a corresponding class in JDBC and each of them extend java.util.Date. These three are:

• java.sql.Date represents SQL DATE, stores years, months and days without time components. java.sql.Date is disregarding timezones.
• java.sql.Time represents SQL TIME and only contains information about hour, minutes, seconds and milliseconds without date component.
• java.sql.Timestamp represents SQL TIMESTAMP which contains both Date and Time information to the nanoseconds precision.

Let’s check below table referring to top 5 databases (not in the order) datetime data types:

I putting the source, my reference in the right most column. Correct me if I’m wrong

Working With JDBC Classes

Which class to choose is depends on the SQL type of the field. PreparedStatement has setters for all three values, setDate() for java.sql.Date, setTime() for java.sql.Time and setTimestamp() for java.sql.Timestamp.

Let’s down to example, we create a test table in our database. For this article, I’ll use MySQL.

CREATE TABLE test_datetime ( dtm_date DATE, dtm_time TIME, dtm_timestamp TIMESTAMP, obj_date DATE, obj_time TIME, obj_timestamp TIMESTAMP );

And then we insert current date/time (get via new java.util.Date()) into the date field of an SQL database table.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class SqlDateTimeInsertExample < public static void main(String[] args) throws Exception < // (1) connect to mysql database String url = "jdbc:mysql://localhost/coffeehouse?serverTimezone=Asia/Singapore"; Class.forName("com.mysql.cj.jdbc.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "cappuccino")) < // (2) set java.sql.Date, Time, and Timestamp with current Date (and time) java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); java.sql.Time sqlTime = new java.sql.Time(utilDate.getTime()); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); // (3) insert java.sql.Date, Time and Timestamp (including objects) to DB String sql = "INSERT INTO test_datetime(" + "dtm_date, dtm_time, dtm_timestamp," + "obj_date, obj_time, obj_timestamp) VALUES (. )"; try (PreparedStatement pst = conn.prepareStatement(sql)) < pst.setDate(1, sqlDate); pst.setTime(2, sqlTime); pst.setTimestamp(3, sqlTS); pst.setObject(4, utilDate); pst.setObject(5, utilDate); pst.setObject(6, utilDate); // (4) execute update pst.executeUpdate(); >> > > 

As in above example, with setObject(int parameterIndex , x Object); we can just give a util.Date to the last three parameters which accept it without problem (this is also happen in another JDBC driver, not only MySQL). But to just use setObject(. ) lazily can cause some problem, including data (or part of data) loss.

Note: The URL suffix ?serverTimezone=Asia/Singapore is to suppress: Exception in thread «main» java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

(Can you see the typo in above MySQL JDBC error message? :p )

Connect to mysql to confirm the result:

$ mysql -u barista -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.27 MySQL Community Server (GPL) mysql> connect coffeehouse Connection id: 10 Current database: coffeehouse mysql> select * from test_datetime; +------------+----------+---------------------+------------+----------+---------------------+ | dtm_date | dtm_time | dtm_timestamp | obj_date | obj_time | obj_timestamp | +------------+----------+---------------------+------------+----------+---------------------+ | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | +------------+----------+---------------------+------------+----------+---------------------+ 1 row in set (0.00 sec)

Liked this Tutorial? Share it on Social media!

Источник

java.sql.Date Examples

The java.sql.Date extends java.util.Date class. java.sql.Date is used in the JDBC API, as a wrapper around java.util.Date that handles SQL specific requirements. This class exists to represent SQL DATE, which keeps years, months and days, with no time. java.sql.Date should be used only when working with databases, like to set a date on a java.sql.PreparedStatement, get a date from a java.sql.ResultSet, working with java.sql.SQLData, etc.

Initialize java.sql.Date

We can instantiate a java.sql.Date object using following constructor:

import java.sql.Date; public class SqlDateInitExample < public static void main(String[] args) < long now = System.currentTimeMillis(); Date sqlDate = new Date(now); System.out.println("currentTimeMillis: " + now); System.out.println("SqlDate : " + sqlDate); System.out.println("SqlDate.getTime(): " + sqlDate.getTime()); >> 

Which will produce (result may vary)

currentTimeMillis: 1563925312859 SqlDate : 2019-07-24 SqlDate.getTime(): 1563925312859

Or, you can use static valueOf(. ) methods to get an instance of java.sql.Date object

• static Date valueOf(String s): Converts a string in JDBC date escape format to a Date value.
• static Date valueOf(LocalDate date): Obtains an instance of Date from a LocalDate object with the same year, month and day of month value as the given LocalDate. This static method available for Java 8 and above.

import java.sql.Date; import java.time.LocalDate; import java.time.Month; public class SqlDateValueOfExample < public static void main(String[] args) < Date sqlDate1 = Date.valueOf("1980-04-09"); System.out.println("SqlDate1: " + sqlDate1); Date sqlDate2 = Date.valueOf(LocalDate.of(1983, Month.JULY, 12)); System.out.println("SqlDate2: " + sqlDate2); >> 

SqlDate1: 1980-04-09 SqlDate2: 1983-07-12

java.sql.Date Methods

Some of methods of java.sql.Date are deprecated. It’s because java.sql.Date extends java.util.Date and thus inherits its deprecated methods, or the methods is unusable for java.sql.Date which only keeps date only, without time. Here some methods available for java.sql.Date:

• void setTime​(long date): Sets an existing Date object using the given milliseconds time value.
• LocalDate toLocalDate(): Creates a LocalDate instance using the year, month and day from this Date object. This method available for Java 8 and above.
• String toString(): Formats a date in the date escape format yyyy-mm-dd.

import java.sql.Date; import java.time.Instant; import java.time.LocalDate; public class SqlDateMethodsExample < public static void main(String[] args) < long now = System.currentTimeMillis(); Date sqlDate = new Date(now); // implicitely call sqlDate.toString() System.out.println("SqlDate : " + sqlDate); sqlDate.setTime(356373000000L); System.out.println("SqlDate : " + sqlDate); LocalDate localDt = sqlDate.toLocalDate(); System.out.println("LocalDate: " + localDt); Instant instant = sqlDate.toInstant(); System.out.println("Instant : " + instant); >> 

Which will produce (result may vary)

SqlDate : 2019-07-24 SqlDate : 1981-04-18 LocalDate: 1981-04-18 Exception in thread "main" java.lang.UnsupportedOperationException at java.sql.Date.toInstant(Unknown Source) at com.dariawan.datetime.SqlDateMethodsExample.main(SqlDateMethodsExample.java:58)

toInstant() method (available for Java 8 and above) always throws an UnsupportedOperationException and should not be used because SQL Date values do not have a time component.

Since java.sql.Date extends java.util.Date, so java.util.Date methods also available for java.sql.Date. Let’s check following example:

import java.sql.Date; public class SqlDateInheritMethodsExample < public static void main(String[] args) < long now = System.currentTimeMillis(); Date sqlDate1 = new Date(now); System.out.println("sqlDate1: " + sqlDate1); Date sqlDate2 = new Date(1586361600000L); System.out.println("sqlDate2: " + sqlDate2); System.out.println(); System.out.println("sqlDate1 after sqlDate2 : " + sqlDate1.after(sqlDate2)); System.out.println("sqlDate2 after sqlDate1 : " + sqlDate2.after(sqlDate1)); System.out.println("sqlDate1 before sqlDate2 : " + sqlDate1.before(sqlDate2)); System.out.println("sqlDate2 before sqlDate1 : " + sqlDate2.before(sqlDate1)); System.out.println("sqlDate1 compareTo sqlDate2: " + sqlDate1.compareTo(sqlDate2)); System.out.println("sqlDate2 compareTo sqlDate1: " + sqlDate2.compareTo(sqlDate1)); >> 

sqlDate1: 2019-07-24 sqlDate2: 2020-04-09 sqlDate1 after sqlDate2 : false sqlDate2 after sqlDate1 : true sqlDate1 before sqlDate2 : true sqlDate2 before sqlDate1 : false sqlDate1 compareTo sqlDate2: -1 sqlDate2 compareTo sqlDate1: 1

Check out java.util.Date for more details.

Conclusion

Although java.sql.Date inherits from java.util.Date, java.sql.Date only keeps the date, not the time. If you need to keep the time, use a java.sql.Timestamp instead.

Liked this Tutorial? Share it on Social media!

Источник