SQLite timestamp problem

In this forum we will discuss things relating the ZEOSLib 6.6.x stable versions

Moderators: gto, EgonHugeist

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

SQLite timestamp problem

Postby jjeffman » 11.06.2013, 22:03

Hello,

I am a little bit new at SQLite.

I have created a SQLite table as follows:

Code: Select all

Create table IF NOT EXISTS EVENTO (
   EVENTO INTEGER NOT NULL  PRIMARY KEY ,
   DATAHORA Timestamp(3,3) NOT NULL ,
   MODULO INTEGER,
   UNIDADE INTEGER   NOT NULL,
   TIPO_EVENTO INTEGER NOT NULL,
) ;

I have inserted a record with the values 1,'',1,1,10 respectively.
I have filled up the SQL property of a TZReadOnlyQuery with the text below:

Code: Select all

SELECT evt.EVENTO
FROM EVENTO evt
 WHERE evt.DATAHORA = :dthr
 AND evt.UNIDADE = :unid
  and evt.TIPO_EVENTO = :tpevt

I could not find any way of passing the DTHR parameter to get the record I have created.
I have tried to pass it as DateTime and also as a String but both options did not retrieve the record.

What am I doing wrong ?

Thank you very much.
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

mmvisual
Senior Boarder
Senior Boarder
Posts: 51
Joined: 13.10.2010, 14:55

Postby mmvisual » 12.06.2013, 19:21

Set this parameter AsFloat and not AsDateTime, then it works good.
SQLite is a simple database and cannot decode all DateTime formats from all counties.

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

I have made some changes

Postby jjeffman » 12.06.2013, 21:13

Thank you for answering. I will give it a try.

I have made some changes which are giving me the proper result.

I have eliminated the date comparison, so I have got more than one row. The Locate method works fine passing the value to search for as DateTime.

I am afraid it is not a SQLite problem because the previous SQL query works perfect on SQLite Expert passing the parameters as strings.

Kind regards
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 03.07.2013, 06:31

jjeffman,

issue resolved?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

Postby jjeffman » 03.07.2013, 13:16

I am still using the locate method, which works fine.

I have found a clue of the problem. I have defined the field "DATAHORA"as timestamp, and there is not this kind of data type on SQLite. I need to store timestamp values, so I have changed the column type to DATETIME. It might be solved. I will check. I had some troubles with date and time comparison also using Oracle queries and the solution was to use Oracle functions to convert string to timestamp and bind parameters as string. I think I can do the same with TZQuery. The problem, I guess, is linked to localization. When you bind parameters as DATETIME the values are converted to datetime strings according to local datetime string and SQLite do not recognize Brazilian datetime formats.

As a matter of fact I can not mark the issue as solved.

Thank you very much.

Kind regards
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

Postby jjeffman » 03.07.2013, 20:37

Hello,

I have changed the SQL query to:

Code: Select all

SELECT evt.EVENTO
FROM EVENTO evt
 WHERE strftime("%Y-%m-%d %H:%M:%f",evt.DATAHORA,'localtime') = strftime("%Y-%m-%d %H:%M:%f", :dthr ,'localtime')
 AND evt.UNIDADE = :unid
  and evt.TIPO_EVENTO = :tpevt


Now I am binding the "dthr" parameter value as a full timestamp formated string and I am getting the proper result.

Kind regards.
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br


Return to “6.6 - stable”

Who is online

Users browsing this forum: No registered users and 1 guest