获取日期之间的数据 [英] Get data between the dates

问题描述

String Date1 = (((JTextField)jDateChooser1.getDateEditor().getUiComponent()).getText());
String Date2 = (((JTextField)jDateChooser2.getDateEditor().getUiComponent()).getText());

String query="SELECT * FROM work_hours WHERE ID ="+A+" AND Date >= "+Date1+" AND Date <= "+Date2+" ";
ResultSet rs = db.Select(query);

这里的Date1和Date2是从用户那里获取的Jdatechooser.

Here Date1 and Date2 is the Jdatechooser, taken from user.

 Error:   
 You have an error in your SQL syntax; check the manual that
 corresponds to your MariaDB server version for the right syntax to use
 near 'Date >= Jun 12, 2017 Date <= Jun 14, 2017' at line 1

推荐答案

日期类型

对于日期时间值，请使用日期时间数据类型定义列，并在Java中使用日期时间类. JDBC驱动程序的工作是在这些类型之间进行中介.

Date-time types

For date-time values, use date-time data types to define your column, and use date-time classes in Java. The job of your JDBC driver is to mediate between these types.

您正在尝试传递字符串而不是日期时间对象.

You are trying to pass strings rather than date-time objects.

在日期时间工作中，请使用Half-Open方法，其中开始是包含在内的，而结尾是排除的.因此，午餐从中午开始，一直持续到但不包括下午1点的第一时刻.一个星期从星期一开始，一直持续到(但包括)下一个星期一.

In date-time work, use Half-Open approach where the beginning is inclusive while the ending is exclusive. So lunch starts at noon and runs up to, but does not include, the first moment of 1 PM. A week starts at Monday and runs up to, but does include, the following Monday.

SELECT * FROM tbl_
WHERE when_ >= ?   -- Pass start moment. Inclusive.
AND   when_  < ?   -- Pass stop moment.  Exclusive.
;

SQL命令BETWEEN是"closed"的，这意味着开始和结束都包括在内；不适合约会时间工作.

The SQL command BETWEEN is "closed" meaning both the beginning and ending are inclusive; not good for date-time work.

您需要将用户输入转换为日期时间对象.您可能要按用户解析字符串类型.或者，您可能要使用日期时间小部件.在您的情况下，显然需要解析字符串.在堆栈溢出中搜索DateTimeFormatter，以找到数百个现有的问题和答案.

You need to transform your user-input into date-time objects. You may want to parse a string types by user. Or you may want to use a date-time widget. In your case, parsing strings is apparently needed. Search Stack Overflow for DateTimeFormatter to find hundreds of existing Questions and Answers.

Java中的Instant类表示UTC时间轴上的时刻.等效于旧版java.util.Date类，但具有几分之一秒而不是毫秒的更好分辨率.

The Instant class in Java represents a moment on the timeline in UTC. Equivalent to the legacy java.util.Date class but with a finer resolution of nanoseconds rather than milliseconds.

应用时区ZoneId以获得ZonedDateTime对象.等效于旧类GregorianCalendar.

Apply a time zone ZoneId to get a ZonedDateTime object. Equivalent to the legacy class GregorianCalendar.

ZonedDateTime zdt = ZonedDateTime.parse( input , … ) ;
myPreparedStatement.setObject( … , zdt.toInstant() ) ;

然后……

Instant instant = myResultSet.getObject( … , Instant.class ) ;
ZonedDateTime zdt = instant.atZone( ZoneId.of( "America/Montreal" ) ) ;

提示

遵守命名约定.在Java中，变量以小写字母开头.

Tips

Observe naming conventions. In Java, variables start with a lowercase letter.

避免用保留字命名数据库中的列.完全避免所有保留字的最简单方法是在所有数据库对象的所有名称后添加下划线. SQL标准明确承诺不要使用下划线.

Avoid naming columns in database with reserved words. Easiest way to entirely avoid all reserved words is to append a trailing underscore to all the names of all your database objects. The SQL standard explicitly promises to never use a trailing underscore.

这篇关于获取日期之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！