searching for proper date and time stamps

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

searching for proper date and time stamps

shamil_daghestani

Hello,

I'm using the TCL binding of sqlite3
One of the columns in my table has a date and time stamp, something like
this:

07/20/2004 01:35:40
06/20/2005 01:37:01
06/20/2005 12:10:07
06/20/2005 12:17:08
06/20/2005 01:35:00

If I want to get all dates that are later than "06/20/2005 01:00:00"  I
might try:

"select c1 from t1 where c1 > '06/20/2005 01:35:00'"

But that also returns All the above including "07/20/2004 01:35:40" because
(I suppose) comparison starts from the left and it sees 07 which is > than
06.  Any ideas or hints in general where I can get the dates that I'm
looking for?

Thanks



The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.

For Translation:

http://www.baxter.com/email_disclaimer

Reply | Threaded
Open this post in threaded view
|

Re: searching for proper date and time stamps

BertV
[hidden email] wrote:

>Hello,
>
>I'm using the TCL binding of sqlite3
>One of the columns in my table has a date and time stamp, something like
>this:
>  
>

datetimes are sorted as strings, maybe that explains
Bert

>07/20/2004 01:35:40
>06/20/2005 01:37:01
>06/20/2005 12:10:07
>06/20/2005 12:17:08
>06/20/2005 01:35:00
>
>If I want to get all dates that are later than "06/20/2005 01:00:00"  I
>might try:
>
>"select c1 from t1 where c1 > '06/20/2005 01:35:00'"
>
>But that also returns All the above including "07/20/2004 01:35:40" because
>(I suppose) comparison starts from the left and it sees 07 which is > than
>06.  Any ideas or hints in general where I can get the dates that I'm
>looking for?
>
>Thanks
>
>
>
>The information transmitted is intended only for the person(s)or entity
>to which it is addressed and may contain confidential and/or legally
>privileged material. Delivery of this message to any person other than
>the intended recipient(s) is not intended in any way to waive privilege
>or confidentiality. Any review, retransmission, dissemination or other
>use of , or taking of any action in reliance upon, this information by
>entities other than the intended recipient is prohibited. If you
>receive this in error, please contact the sender and delete the
>material from any computer.
>
>For Translation:
>
>http://www.baxter.com/email_disclaimer
>
>
>
>  
>

Reply | Threaded
Open this post in threaded view
|

Re: searching for proper date and time stamps

lawrence.chitty
In reply to this post by shamil_daghestani
[hidden email] wrote:

>Hello,
>
>I'm using the TCL binding of sqlite3
>One of the columns in my table has a date and time stamp, something like
>this:
>
>07/20/2004 01:35:40
>06/20/2005 01:37:01
>06/20/2005 12:10:07
>06/20/2005 12:17:08
>06/20/2005 01:35:00
>
>If I want to get all dates that are later than "06/20/2005 01:00:00"  I
>might try:
>
>"select c1 from t1 where c1 > '06/20/2005 01:35:00'"
>
>But that also returns All the above including "07/20/2004 01:35:40" because
>(I suppose) comparison starts from the left and it sees 07 which is > than
>06.  Any ideas or hints in general where I can get the dates that I'm
>looking for?
>
>Thanks
>
>  
>
Either store your datetime stamp as YYYY/MM/DD HH:MM:SS, eg 2005/06/20
11:23:35 so that the string comparison works on them as expected, or use
the Unix time format, e.g number of seconds since Jan 1 1970, which Tcl
will happily convert to and from using clock scan and clock format.
There are also some fucntions in Sqlite that can deal with this format -
see http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Lawrence

>
>The information transmitted is intended only for the person(s)or entity
>to which it is addressed and may contain confidential and/or legally
>privileged material. Delivery of this message to any person other than
>the intended recipient(s) is not intended in any way to waive privilege
>or confidentiality. Any review, retransmission, dissemination or other
>use of , or taking of any action in reliance upon, this information by
>entities other than the intended recipient is prohibited. If you
>receive this in error, please contact the sender and delete the
>material from any computer.
>
>For Translation:
>
>http://www.baxter.com/email_disclaimer
>
>
>
>  
>