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:
| Database | SQL DATE | SQL TIME | SQL TIMESTAMP | Source |
|---|---|---|---|---|
| MySQL / MariaDB | DATE DATETIME | TIME | TIMESTAMP | Link Link |
| PostgreSQL | DATE | TIME TIME WITH TIME ZONE | TIMESTAMP TIMESTAMP WITH TIME ZONE | Link |
| Oracle | DATE | TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE | Link | |
| Microsoft SQL Server | DATE SMALLDATETIME DATETIME DATETIME2 DATETIMEOFFSET | TIME | Link | |
| IBM Db2 | DATE | TIME | TIMESTAMP | Link |
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.
SqlDateTimeInsertExample.java
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)
Desson AriawanProgrammer