Problem with SQL Native

[ORibeiro]

Hello Friends,

With this command in oQry:Query() in MySQL
WHERE cases.date BETWEEN '2022-01-01 00:00:00' AND '2022-01-12 23:59:59';
Return 31 records, the last two with date '2022-01-13 00:47:50'

Using the same Select in HeidiSql, 29 records are returned correctly within the period 01 to 12 January.

Has anyone had this same problem with the native class?

Thanks.

[nageswaragunupudi]

Has anyone had this same problem with the native class?

No.
I will prepare a sample to test and post here soon.

One point, though not relating to your issue:
Do not use BETEEN while dealing with DateTime Fields.
For example, "AND 2022-01-12 23:59:59" excludes timestamp 2022-01-12 23:59:59.100, though this is still 12th Jan.
Better <field> >= <date1> and <field> < ( <date2> + 1 )

[ORibeiro]

Thanks Raw,

I modified to:
WHERE date(casos.data)>=date('2022-01-01') AND date(casos.data)<date('2022-01-12')+1;

The result is 31 records, like before.

There are two records in casos.data = date('2022-01-13') in the result.

I don´t know why.

Do you have another idea?

[nageswaragunupudi]

Is casos.data a DateTime field?
Why do you use Date(casos.data) ?
Please let us know the exact field type of casos.data

[ORibeiro]

Hi Raw,

The type of field is TIMESTAMP.

If I don´t put date(casps.data) the query does´t work and return all records.

[ORibeiro]

Hello,

I would like to know if this issue in select with TIMESTAMP field has been fixed in newer versions of FWH.

The same SELECT in SQLRDD and HeidiSQL brings the correct records, but in native mode it brings records from other dates.

Thanks.

[nageswaragunupudi]

Code: Select all  Expand view

"SELECT ...... WHERE casps.data >= '2022-01-01' AND casps.data < '2022-01-13'"
 

[ORibeiro]

Unfortunately it didn't work.
Could the problem be with my DLL version?

[Jimmy]

hi,

did you use

Code: Select all  Expand view

ORDER BY mytimestamp

when "compare" TIMESTAMP

as i know ORDER BY use UTC but you want "Local Time" so try

Code: Select all  Expand view

ORDER BY CAST(mytimestamp AS datetime)

[nageswaragunupudi]

We created a small table `test_timestamp` on our Fivewin Cloud server, which is accessible to all of us for demo purposes.

This is the full table data.



We want to extract rows with timestamp between 01-02-2022 and 04-02-2022 (both days inclusive). We used where clause like this:

Code: Select all  Expand view

SELECT * FROM test_timestamp WHERE col_timestamp >= '2022-02-01' AND col_timestamp < '2022-02-05'
 

This is the result:


You can yourself run this program. Copy the program given below to \fwh\samples folder and build and run with buildh.bat or buildx.bat.

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, oRs
   local t

   SET DATE ITALIAN
   SET CENTURY ON

   oCn   := FW_DemoDB( 6 )
   if oCn == nil
      ? "connect fail"
      return nil
   endif
   oCn:lShowErrors := .t.

   oRs   := oCn:RowSet( "SELECT * FROM test_timestamp" )
   XBROWSER oRs TITLE "FULL TABLE"
   oRs:Close()

   oRs   := oCn:RowSet( "SELECT * FROM test_timestamp WHERE col_timestamp >= '2022-02-01' AND col_timestamp < '2022-02-05'" )
   XBROWSER oRs TITLE "01 FEB TO 04 FEB"
   oRs:Close()

   oCn:Close()

return nil
 

You can even modify the program and test whatever you like.

This is the structure of the table:

Code: Select all  Expand view

CREATE TABLE `test_timestamp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `COL_DATETIME` DATETIME(3) DEFAULT NULL,
  `COL_TIMESTAMP` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

[nageswaragunupudi]

hi,

did you use

Code: Select all  Expand view

ORDER BY mytimestamp

when "compare" TIMESTAMP

as i know ORDER BY use UTC but you want "Local Time" so try

Code: Select all  Expand view

ORDER BY CAST(mytimestamp AS datetime)

It is not necessary to ORDER BY THE field for setting WHERE clause on that field.
We need to consider UTC offset if and only if we are accessing the same database over internet from different timezones/countries.

[nageswaragunupudi]

hi,

did you use

Code: Select all  Expand view

ORDER BY mytimestamp

when "compare" TIMESTAMP

as i know ORDER BY use UTC but you want "Local Time" so try

Code: Select all  Expand view

ORDER BY CAST(mytimestamp AS datetime)

It is not necessary to ORDER BY THE field for setting WHERE clause on that field.
We need to consider UTC offset if and only if we are accessing the same database over internet from different timezones/countries.

[Rick Lipkin]

Jimmy

I know you mentioned Sql Native .. MS Access has a bit different syntax .. you have to put # around your date variables

Code: Select all  Expand view

"[InvoiceDate] >= #"+dtoc(lDate)+"# And [InvoiceDate] <= #"+dtoc(hDate)+"#"

Otherwise

Code: Select all  Expand view

"[InvoiceDate] >= '"+DTOC(LDATE)+"' and [InvoiceDate] <= '"+DTOC(HDATE)+"'"

You have to use ' ' around your date variables.

Rick Lipkin

[nageswaragunupudi]

Jimmy

I know you mentioned Sql Native .. MS Access has a bit different syntax .. you have to put # around your date variables

Code: Select all  Expand view

"[InvoiceDate] >= #"+dtoc(lDate)+"# And [InvoiceDate] <= #"+dtoc(hDate)+"#"

Otherwise

Code: Select all  Expand view

"[InvoiceDate] >= '"+DTOC(LDATE)+"' and [InvoiceDate] <= '"+DTOC(HDATE)+"'"

You have to use ' ' around your date variables.

Rick Lipkin

DTOC() does not work for MySql.
Another thing, there a few things which work for Americans but not others in the world. because some database servers accept American notation of dates.
For example your syntax of DTOC() does not work for me in India even on MSACCESS.
In these forums, please post solutions which are Universal.

[ORibeiro]

Raw,

I tried to run your example but returned the error: 1045 Acess denied for user 'sql7148817@'189-46-180-40.dsl.telesp.net.br (using password: YES).

Thanks