Recent

Author Topic: Tsqlitedataset and time format as hh:mm:ss  (Read 9017 times)

Sainty

  • Guest
Tsqlitedataset and time format as hh:mm:ss
« on: December 20, 2005, 09:32:11 am »
Hello all,

1. I'm inserting data to field StartTime (defined in table 'calls' as Time type):
...
SqliteDataSet.FieldByName('StartTime').AsDateTime   := StrToTime('18:59:59');
....
after all, using 'SELECT StartTime from calls' I get in my dbGrid time in hh:mm format (without seconds). How I can get it displayed with seconds?

2.  How to search database using time type? (SELECT)

Kind regards,

Sainty

JanH

  • Guest
RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #1 on: December 21, 2005, 08:30:05 am »
Hi Sainty,

1. In TDBGrid each column has a property named DisplayFormat. Use 'hh:mm:ss' to display hour:minute:second. Either you set this property value at design time, or you dynamically set it at runtime.

2. Try statements like this:
 
Code: [Select]
SELECT * FROM calls WHERE starttime='10:11:12'
SELECT * FROM calls WHERE starttime>='10:11' and starttime<='10:12'
 


Greets,

Jan

Marc

  • Administrator
  • Hero Member
  • *
  • Posts: 2591
RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #2 on: December 21, 2005, 10:39:24 am »
or better (if the db supports it)
Code: [Select]
SELECT * FROM calls WHERE starttime BETWEEN '10:11:00' and '10:12:00'
//--
{$I stdsig.inc}
//-I still can't read someones mind
//-Bugs reported here will be forgotten. Use the bug tracker

Anonymous

  • Guest
Re: RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #3 on: December 23, 2005, 06:17:15 pm »
Quote from: "Marc"
or better (if the db supports it)
Code: [Select]
SELECT * FROM calls WHERE starttime BETWEEN '10:11:00' and '10:12:00'


This wont work.
TSqliteDataset stores TDateTime as a float value.

Marc

  • Administrator
  • Hero Member
  • *
  • Posts: 2591
RE: Re: RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #4 on: December 27, 2005, 11:22:23 am »
thats why I wrote:
Quote
(if the db supports it)
:-)
It is more a generic remark that when a db supports it it is better to use
  b BETWEEN a and c
than
  (a < b) and (b < c)

since the first can be done with a sinlge index lookup.
//--
{$I stdsig.inc}
//-I still can't read someones mind
//-Bugs reported here will be forgotten. Use the bug tracker

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 457
RE: Re: RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #5 on: January 05, 2006, 12:13:03 am »
Sqlite supports the BETWEEN operator. What i said is that TSqliteDataset, which is a fpc wrapper around sqlite, stores the float value (something like 1,23700000+E10) instead of the textual representation (11:00:00) so it will compare different formats. Maybe this will work (not tested):

'SELECT * FROM calls WHERE starttime BETWEEN '+ FloatToStr(StrToTime('10:11:00'))+
' and '+ FloatToStr(StrToTime('10:12:00'))

Luiz

Anonymous

  • Guest
RE: Re: RE: Tsqlitedataset and time format as hh:mm:ss
« Reply #6 on: January 17, 2006, 12:05:35 pm »
Thanks for your help. It's stored in float value and it works fine now on SQLite 2.x.

Sainty

 

TinyPortal © 2005-2018