英文:
how to convert from LocalDateTime (java) to datetime (sql)?
问题
如何将LocalDateTime（java）转换为datetime（sql）？
英文:
how to convert from LocalDateTime (java) to datetime (sql) ?
答案1
得分: 3
'java.sql'包中的'SQL native'时间类型有点糟糕 - 没有任何东西表示 '没有时区信息的日期和时间'。
数据库也有各种不同的存储方式。存储它的合适方式是将年、月、日、小时、分钟、秒，以及可能的毫秒/纳秒都作为单独的实体进行存储（并完全放弃尝试通过某个时刻以来的毫秒数来进行转换的概念）。
幸运的是，JDBC确实提供了供数据库驱动程序使用自定义数据类型的基础设施。
例如，PSQL驱动程序允许您编写以下代码：
somePreparedStatementObject.setObject(1, yourLocalDateTimeObject);
这可以正常工作，前提是您将其用于适当的地方（例如，作为插入语句中某列的值，其类型为TIMESTAMP WITHOUT TIMEZONE）。
直接获取这样的字段也是可能的：
someResultSet.getObject(1, LocalDateTime.class);
这将直接获得一个LocalDateTime。
这样可以避免复杂的时区问题，而将其转换，这是所有'java.sql'时间/日期类型都会出现的问题！
因此，首先尝试上述方法。如果您的数据库引擎有点糟糕，不允许您执行上述操作，那么可以尝试转换，可能是通过UTC，但如果您的数据库服务器和JVM在配置了不同的 '系统默认时区' 的机器上运行，或者在冬季设置/查询夏季日期时可能会出现日期突然不一致的情况，请不要感到惊讶。
英文:
The 'SQL native' time types in the java.sql packages are kinda bad - there is nothing in there that represents 'a date and a time without any timezone information'.
Databases also have wildly different ways of storing such information. The appropriate way to store it, is to store the year, month, day, hour, minute, second, and potentially, milli/nanos all as separate entities (and to forego entirely the notion of trying to go via millis-since-some-epoch-someplace).
Fortunately, JDBC does offer infrastructure for database drivers to use custom datatypes.
For example, the PSQL driver lets you write this:
somePreparedStatementObject.setObject(1, yourLocalDateTimeObject);
This works.. IF you're using it in a place that is appropariate (for example, as value in an INSERT statement for a column whose type is TIMESTAMP WITHOUT TIMEZONE).
Getting such a field out is also possible, directly:
someResultSet.getObject(1, LocalDateTime.class);
This gets you a LocalDateTime, directly.
Thus sidestepping hairy timezone issues, which occur if you convert, which all the java.sql time/date types do!
So, try the above first. If your DB engine is kinda cruddy and won't let you do the above, okay, then convert, presumably via UTC, but don't be surprised if your dates are all of a sudden off by a day if your DB server and the JVM are running on machines with different configs for 'system default timezone', or for weirdness to ensue when it's winter and you're setting/querying a date in summer.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论