On 26-10-2018 08:42, Mark Rotteveel mark@... [Firebird-Java]
wrote:

> On 2018-10-26 00:31, shalamyansky@... [Firebird-Java] wrote:
>> Server:
>> Windows Server 2012 64
>> Firebird 3.0
>> Moscow Time Zone (+0300)
>> Fields type DATE
>>
>> Client:
>> Java Jdk (Jre) 8.0
>> Spring JPA
>> Jaybird-jdk18 3.0.4
>> Moscow Time Zone (+0300)
>> Objects type Date
>>
>> Problem:
>>
>> All the Java.Date variables values are shifted to previos day of real
>> Firebird.DATE fields values.
>>
>> I suppose Jaybird or somebody else accepts DATE as TIMESTAMP,
>> '2018-10-25' as '2018-10-25 00:00:00', treats its as '2018-10-25
>> 00:00:00 +0300', transforms its into '2018-10-24 21:00:00 +0300', and
>> returns as Date '2018-10-24'. Maybe another logic of time zone
>> calculating. I'd like work with pure dates without zones, hours and
>> date shifting.
>>
>> My question:
>>
>> How to fix this matter in one place, preferably somewhere in the
>> configuration?
>
> Excluding any time zone behavior is impossible with java.sql.Date, as
> its definition is based on java.util.Date, and it requires a form of
> time zone manipulation to obtain values.
>
> Could you provide a more complete reproduction of the problem, so I can
> narrow down the problem, because right now I can think of several
> scenarios that might cause this (and some of them could be bugs in
> Jaybird).
>
> I'd like to know:
>
> - The dialect of the database
> - The connection properties used
> - Example code how you retrieve the value and determine that its value
> is shifted (preferably in the form of a MCVE).
> - The default JVM timezone (result of TimeZone.getDefault() and/or
> ZoneId.systemDefault()
>
> Also consider using java.time types (java.time.LocalDate in this case)
> instead.

As an example, for me all these behave as expected in Europe/Amsterdam
(GMT+02:00):

try (Connection connection = DriverManager.getConnection(

"jdbc:firebirdsql://localhost/D:/data/db/fb3/fb3testdatabase.fdb",
"sysdba", "masterkey");
PreparedStatement pstmt = connection.prepareStatement(
"select date'2018-10-26', "
+ "cast(? as date), "
+ "cast(cast(? as date) as varchar(25)), "
+ "timestamp'2018-10-26 00:00:00' "
+ "from rdb$database")) {

System.out.println("Now: " + LocalDate.now());
System.out.println("TimeZone: " + TimeZone.getDefault());
System.out.println("ZoneId: " + ZoneId.systemDefault());

pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));

try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
java.sql.Date date1 = rs.getDate(1);
java.sql.Date date2 = rs.getDate(2);
java.sql.Date date3 = rs.getDate(3);
String date3AsString = rs.getString(3);
java.sql.Date date4 = rs.getDate(4);

System.out.println("Date1: " + date1);
System.out.println("Date2: " + date2);
System.out.println("Date3: " + date3);
System.out.println("Date3 as string: " + date3AsString);
System.out.println("Date4: " + date4);
}
}
}

Output:

Now: 2018-10-26
TimeZone:
sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]
ZoneId: Europe/Berlin
Date1: 2018-10-26
Date2: 2018-10-26
Date3: 2018-10-26
Date3 as string: 2018-10-26
Date4: 2018-10-26

--
Mark Rotteveel