Re: [sqlite] Date and time comparison
Karthick V - TLS , Chennai wrote:
Hello everyone,
I am trying to retrieve some records from the database using date and time
comparison. The table has three columns, Row Id, Start Time and end Time.
I need to get the row id for a time which falls within the start and end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose start time
is closest to the given time. Say the start times are 12:10 and 12:20 and
the given time is 12:25, it should return the second record only.
I am trying the following query
strftime('%Y-%m-%dT%H:%M',endtime - starttime)
however this fails giving some negative numbers.
If anyone knows of a solution, kindly post a reply for this. Thanks in
advance.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended for the
named recipient(s) only. It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in this email are solely those of the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,
dissemination, copying, disclosure, modification, distribution and / or publication of this
message without the prior written consent of the author of this e-mail is strictly
prohibited. If you have received this email in error please delete it and notify the sender
immediately. Before opening any mail and attachments please check them for viruses and
defect.
What form do you use to store the date and time?
Do you use the Sqlite date format?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Date and time comparison
Hello everyone,
I am trying to retrieve some records from the database using date and time
comparison. The table has three columns, Row Id, Start Time and end Time.
I need to get the row id for a time which falls within the start and end
time.
I am using this query.
select RowID,
strftime('%Y-%m-%dT%H:%M:%S',starttime),
strftime('%Y-%m-%dT%H:%M',endtime) from History where
strftime('%Y-%m-%dT%H:%M',starttime) <
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and
strftime('%Y-%m-%dT%H:%M',endtime) >
strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17');
This works well if there is only one matching row.
However if more than one row matches, I need to get the row whose start time
is closest to the given time. Say the start times are 12:10 and 12:20 and
the given time is 12:25, it should return the second record only.
I am trying the following query
strftime('%Y-%m-%dT%H:%M',endtime - starttime)
however this fails giving some negative numbers.
If anyone knows of a solution, kindly post a reply for this. Thanks in
advance.
With Regards
Karthick V
"The secret of being miserable is to have leisure to bother about whether
you are happy or not. The cure for it is occupation."
George Bernard Shaw (1856-1950)
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended
for the
named recipient(s) only. It shall not attach any liability on the originator or
HCL or its
affiliates. Any views or opinions presented in this email are solely those of
the author and
may not necessarily reflect the opinions of HCL or its affiliates. Any form of
reproduction,
dissemination, copying, disclosure, modification, distribution and / or
publication of this
message without the prior written consent of the author of this e-mail is
strictly
prohibited. If you have received this email in error please delete it and
notify the sender
immediately. Before opening any mail and attachments please check them for
viruses and
defect.
