Daylight savings |
Andrew |
2019-09-16 17:16:08 |
We have Foxpro 2.01 DBF application and field is type DATE. When open raw table it only shows date (e.g. "4/1/2004") When query using Hxtt DBF driver it seems to convert to timestamp and also seems to apply daylight savings time (e.g. {ts '2004-04-02 00:00:00'} and {ts '2004-04-05 01:00:00'} - and I note that daylight savings was April 4 in 2004).
The issue is that we are having boundry issues when using a BETWEEN in WHERE clause because of that added hour in the date field. I have tried using DATE() function to just get date but it does not seem to work.
We are using Hxtt driver with Lucee/ColdFusion. How to fix?
|
Re:Daylight savings |
HXTT Support |
2019-09-17 04:01:35 |
Tried
select timestamp('4/1/2004'),date('4/1/2004'), {ts '2004-04-02 00:00:00'}, {ts '2004-04-05 01:00:00'} ;
create table if not exists testdate (d1 date,d2 timestamp);
insert into testdate values('4/1/2004','4/1/2004');
select * from testdate;
It seems normal.
What's your code?
|
Re:Daylight savings |
HXTT Support |
2019-09-17 04:14:52 |
|
Default it will use Calendar.getInstance()
|
Re:Re:Daylight savings |
HXTT Support |
2019-09-17 04:21:23 |
Calendar.java
public static Calendar getInstance()
{
return createCalendar(TimeZone.getDefault(), Locale.getDefault(Locale.Category.FORMAT));
}
|
Re:Re:Re:Daylight savings |
HXTT Support |
2019-09-17 04:23:04 |
|
What's your sql? HXTT DBF should use it as a java.sql.Date object, not a java.sql.Timestamp object.
|
Re:Re:Re:Re:Daylight savings |
HXTT Support |
2019-09-17 05:21:14 |
|
BTW, you can use {d '2004-04-01'} for date comparation.
|
Re:Re:Re:Re:Re:Re:Daylight savings |
Andrew Kretzer |
2019-09-17 11:22:05 |
Here is example of our SQL:
SELECT DATE, SO_NO
FROM table
WHERE DATE BETWEEN {d '2004-04-01'} AND {d '2004-04-05'}
The above SQL does not return rows where DATE is from 4/5/2004. However, this will give me rows from 4/5/2004:
SELECT DATE, SO_NO
FROM table
WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}
|
Re:Re:Re:Re:Re:Re:Re:Daylight savings |
HXTT Support |
2019-09-17 17:01:15 |
Tried
create table if not exists testdate (d1 date,d2 timestamp);
insert into testdate values('4/1/2004','4/1/2004');
insert into testdate values('4/5/2004','4/5/2004');
select * from testdate;
select * from testdate WHERE d1 BETWEEN {d '2004-04-01'} AND {d '2004-04-05'} ;
select * from testdate WHERE d2 BETWEEN {d '2004-04-01'} AND {d '2004-04-05'} ;
No issue.
Please use create table asample SELECT DATE, SO_NO FROM table WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}
to get asample file, then email support @ hxtt.com that file.
|
Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings |
Andrew |
2019-09-17 17:54:17 |
|
Emailed file to you. Same problem.
|
Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings |
Andrew |
2019-09-17 18:06:28 |
|
So it appears to work when I use straight SQL and fails when I use parameterized query?
|
Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings |
HXTT Support |
2019-09-18 03:03:29 |
You send me a testdata.dbf, not your
create table asample SELECT DATE, SO_NO FROM table WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}
> So it appears to work when I use straight SQL and fails when I use parameterized query?
Then it's wrong for
select * from testdate WHERE d2 BETWEEN ? AND ? ;
?
If so, you can try your jdbcurl?timezone=local or timezone=GMT-8 .
Maybe your program created a wrong java.sql.Date object?
You can also try setString(yourParameterIndex,"2004-04-05");
|
Main
webmaster@hxtt.com