Re: [sqlite] Date and time in RFC 822

2008-07-08 Thread Dennis Cote
Wojciech wrote:
> I would ask, if there is any possibility to sort data in sqlite tables by
> date, which is stores in RFC 822 format. I have data in this format, which
> comes from RSS channels - in RSS specification RFC 822 it's required.
> 
> Sample date looks like that: Sat, 07 Sep 2002 00:00:01 GMT
> 

I think you will have to create a custom function that converts your RFC 
822 date strings into ISO 8601 date strings. The ISO 8601 strings will 
be sorted into date order by a simply lexical sort.

   SELECT MAX(RFC822toISO8601(date_field)) FROM table

User defined functions are described at http://www.sqlite.org/capi3.html

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Date and time in RFC 822

2008-07-08 Thread Wojciech
Hi,

I would ask, if there is any possibility to sort data in sqlite tables by
date, which is stores in RFC 822 format. I have data in this format, which
comes from RSS channels - in RSS specification RFC 822 it's required.

Sample date looks like that: Sat, 07 Sep 2002 00:00:01 GMT

I tried with something like 

SELECT MAX(date(date_field)) FROM table

...but it isn't working. I readed
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions and searched a
couple of web resources, but I didn't find any working solution.

Thank You kindly for any help,

WK


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Date and time comparison

2006-11-17 Thread John Stanton

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

2006-11-17 Thread Karthick V - TLS , Chennai
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.


Re: [sqlite] Date and time

2006-11-08 Thread John Stanton
Your machine has the timezone recorded and localtime uses that to make 
the appropriate correction from UTC (Greenwich or Zulu) time.


This approach makes the time correct if you are operating across time 
zones.  It is the way your Unixepoch time expects to work.  A machine 
which gives the correct time in New York gives incorrect time in Seattle 
unless you use UCT and apply the New York and Seattle time zones.


If you use a Unix machine you will see an environment variable TZ with a 
value like  TZ=EET-10EETDT-11,J298,J90 which encodes the time zone, the 
offset from UCT, the daylight savings zone and offset and the julian 
days of the switch in and out of summer time.


When a user logs in their profile can override the default time zone.

Windows does a similar thing when you set up the OS and define the time 
zone by selecting it on a map.


Lloyd wrote:

Hi,

I felt sqlite's date and time functions are very useful and suits my
needs.

But still am not clear about one thing. I gave the following query

select datetime(1162961284,'unixepoch');

2006-11-08 04:48:04

The date is correct but the time is 5 hours lagging. So for local time
correction I gave the following query

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 


My problem is, I am getting these time stamps from network packets, And
I want to  display it accurately. (as in the localtime).

Which way can I prefer ?

Thanks,
  Lloyd.



__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Date and time

2006-11-07 Thread Lloyd

Thanks Craig Morrison. Now I got the idea clearly.

On Wed, 2006-11-08 at 00:55 -0500, Craig Morrison wrote:
> Lloyd wrote:
> >  select datetime(1162961284,'unixepoch','localtime');
> > 
> >  2006-11-08 10:18:04
> > 
> > The result is correct.
> > 
> > I would like to know how sqlite is performing the localtime correction. 
> 
> timestamps are in relation to UTC.. When you use the localtime modifier, 
> you are instructing the code to adjust the timestamp display in 
> accordance with your local settings. IOW, the offset from UTC of your 
> system is used in the calculation of the displayed date.
> 
> Google: "strftime", "asctime" and "localtime" for a better explanation.
> 


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Date and time

2006-11-07 Thread Craig Morrison

Lloyd wrote:

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 


timestamps are in relation to UTC.. When you use the localtime modifier, 
you are instructing the code to adjust the timestamp display in 
accordance with your local settings. IOW, the offset from UTC of your 
system is used in the calculation of the displayed date.


Google: "strftime", "asctime" and "localtime" for a better explanation.

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Date and time

2006-11-07 Thread Lloyd
Hi,

I felt sqlite's date and time functions are very useful and suits my
needs.

But still am not clear about one thing. I gave the following query

select datetime(1162961284,'unixepoch');

2006-11-08 04:48:04

The date is correct but the time is 5 hours lagging. So for local time
correction I gave the following query

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 

My problem is, I am getting these time stamps from network packets, And
I want to  display it accurately. (as in the localtime).

Which way can I prefer ?

Thanks,
  Lloyd.



__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Date and Time functions are experimental

2004-01-06 Thread Roger Reghin
That's what I call "clarification" !!!  =)

Thank you!!

Roger.



- Original Message - 
From: "Doug Currie" <[EMAIL PROTECTED]>
To: "Roger Reghin" <[EMAIL PROTECTED]>
Cc: "W Allan Edwards" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, January 05, 2004 7:40 PM
Subject: Re: [sqlite] Date and Time functions are experimental


Here are some clarifications about date and time functions.

SQLite stores everything in the database as a string. You may choose
to represent your datetimes in the database as Julian dates or as
datetime formatted strings. Julian dates are doubles, but they are
converted to and from string when written to and read from the
database.

Representing your datetimes as Julian dates has the advantages that
1. you get the most time resolution for the fewest bytes
2. you can compute datetime differences with simple aritmetic
3. it is the "native" format inside the SQLite date and time functions
4. it is the format returned from the internal now() function
and the disadvantages that
1. conversion to datetime formatted strings needed for human consumption
2. queries against hand coded dates also need conversion to be readable

Representing your datetimes as datetime formatted strings has the
converse advantages and disadvantages.

Either format may be passed directly as the first argument to the date
and time functions: julianday, date, time, datetime, and the 2nd
argument to strftime. Of course passing a Julian date to julianday (or
a datetime formatted string to datetime) is redundant unless you are
also passing modifiers.

Now, the query

SELECT DateTimeField FROM Allan
 WHERE datetime(DateTimeField) = datetime('2004-01-04 12:00:00');

does some extra work. Since datetime returns a datetime formatted
string, a simpler query with the same effect is:

SELECT DateTimeField FROM Allan
 WHERE datetime(DateTimeField) = '2004-01-04 12:00:00';

This query makes sense if your DateTimeField is stored as a Julian
date. If DateTimeField is stored as a datetime formatted string, you
might as well just say:

SELECT DateTimeField FROM Allan
 WHERE DateTimeField = '2004-01-04 12:00:00';

The alternative, when DateTimeField is stored as a Julian date, is:

SELECT DateTimeField FROM Allan
 WHERE DateTimeField = julianday('2004-01-04 12:00:00');

See transcript below.

e

sqlite> insert into Allan values ('2004-01-04 12:00:00');
sqlite> insert into Allan values (julianday('2004-01-04 12:00:00'));
sqlite> select * from Allan;
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
   ...>  WHERE datetime(DateTimeField) = '2004-01-04 12:00:00';
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
   ...>  WHERE datetime(DateTimeField) = datetime('2004-01-04 12:00:00');
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
   ...>  WHERE DateTimeField = '2004-01-04 12:00:00';
2004-01-04 12:00:00
sqlite> SELECT DateTimeField FROM Allan
   ...>  WHERE DateTimeField = julianday('2004-01-04 12:00:00');
2453009
sqlite>

-=-

Monday, January 5, 2004, 11:56:13 AM, you wrote:

> Got it!! =)

> Thank you so much again!!

> Roger.


>   - Original Message - 
>   From: W Allan Edwards
>   To: [EMAIL PROTECTED]
>   Sent: Monday, January 05, 2004 12:53 PM
>   Subject: Re: [sqlite] Date and Time functions are experimental


>   By my understanding of the code, it is turing into into a number that
represents a Julian date.  This number is what is compared.  Although it
translates all the strings, it turns them into
> numbers because comparisons are much faster.

>   Basically, I think that is what the datetime and other functions are
doing.  The statement evaluates the functiosn for each row which translates
the datetime strings into numbers, then the
> compareter is run.

>   Previous to the Dr. adding these routines, you had to store a number
period and do your own date translations.  That is nasty if you manually do
things with the database.  It is much more
> convenient to have these routines.  As well, perf ormance in much better
on date time queries.

>   Thanks,

>   Allan



>   >From: "Roger Reghin"
>   >To: "'SQLite Mailingliste'"
>   >Subject: Re: [sqlite] Date and Time functions are experimental
>   >Date: Mon, 5 Jan 2004 13:37:37 -0300
>   >
>   >Understood!!
>   >
>   >Now, let me ask you something else about this subject...
>   >
>   >When comparing values, does SQLite transform the DateTime value to a
string
>   >before comparing?
>   >
>   >For example, as Allan wrote:
>   >
>   >   SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) =
>   >datetime('2004-01-04 12:00:00');
>   >
>   >So, before comparing "datetime(DateTimeField) = datetime('2004-01

Re: [sqlite] Date and Time functions are experimental

2004-01-05 Thread D. Richard Hipp
Kurt Welgehausen wrote:
DRH wrote

The date and time functions ... are fully tested ...

I'm using SQLite version 2.8.8, precompiled binaries, on Linux.
The date/time functions seem to work, but some of the modifiers
don't.  Specifically, 'gregorian', 'julian', 'start of week',
'localtime', and 'utc' all cause the functions to return NULL;
also 'N months' and 'N years' zero the time (maybe that's
intentional).
The wiki documentation is out of date.

Here are some examples cut and pasted from an interactive
session:
SELECT julianday('1066-10-14','gregorian');

The 'gregorian' and 'julian' modifiers are not implemented.

Feel free to update the wiki to record this fact.

sqlite> select datetime('now', 'start of week');

The 'start of week' modifier is replaced by 'weekday N' (where
N is a digit between 0 and 6.  0==Sunday, 1==Monday, etc.)
This advances the date to the beginning of the next weekday
identified.  See comments in the source code for details.
Feel free to update the Wiki.

sqlite> select datetime('now', 'localtime');

sqlite> select datetime('2004-01-05 00:00:00', 'localtime');

sqlite> select datetime(2453010, 'localtime');

All these work in the latest version under CVS.  But not
in the 2.8.8 release tarball.
sqlite> select datetime(2453010);
2004-01-05 12:00:00
sqlite> select datetime('now', '20 minutes');
2004-01-05 15:41:02
What were you expecting to see here?  I say the two
example above are working correctly.
sqlite> select datetime('now', '2 months');
2004-03-05 00:00:00
I suppose you could argue that this should preserve
the HH:MM:SS.  Write a ticket for this one.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Date and Time functions are experimental

2004-01-05 Thread Roger Reghin
Got it!! =)

Thank you so much again!!

Roger.


  - Original Message - 
  From: W Allan Edwards 
  To: [EMAIL PROTECTED] 
  Sent: Monday, January 05, 2004 12:53 PM
  Subject: Re: [sqlite] Date and Time functions are experimental


  By my understanding of the code, it is turing into into a number that represents a 
Julian date.  This number is what is compared.  Although it translates all the 
strings, it turns them into numbers because comparisons are much faster.

  Basically, I think that is what the datetime and other functions are doing.  The 
statement evaluates the functiosn for each row which translates the datetime strings 
into numbers, then the compareter is run.

  Previous to the Dr. adding these routines, you had to store a number period and do 
your own date translations.  That is nasty if you manually do things with the 
database.  It is much more convenient to have these routines.  As well, perf ormance 
in much better on date time queries.

  Thanks,

  Allan



  >From: "Roger Reghin" 
  >To: "'SQLite Mailingliste'" 
  >Subject: Re: [sqlite] Date and Time functions are experimental 
  >Date: Mon, 5 Jan 2004 13:37:37 -0300 
  > 
  >Understood!! 
  > 
  >Now, let me ask you something else about this subject... 
  > 
  >When comparing values, does SQLite transform the DateTime value to a string 
  >before comparing? 
  > 
  >For example, as Allan wrote: 
  > 
  >   SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) = 
  >datetime('2004-01-04 12:00:00'); 
  > 
  >So, before comparing "datetime(DateTimeField) = datetime('2004-01-04 
  >12:00:00')", does SQLite transform both to string and then do the compare, 
  >or does it transform both to floating point and then compare them? 
  > 
  >I ask that because I know thereĀ“s an overhead doing the typecast, so I would 
  >like to choose what costs less, in my case. 
  > 
  >Thanx!! 
  > 
  >Roger. 
  > 
  > 
  > 
  >- Original Message - 
  >From: "D. Richard Hipp" 
  >To: "'SQLite Mailingliste'" 
  >Sent: Monday, January 05, 2004 11:37 AM 
  >Subject: [sqlite] Date and Time functions are experimental 
  > 
  > 
  > > Allan Edwards wrote: 
  > > > I looked through the source code and those functions "WERE" 
  >experimental. 
  > > > They appear to be a full non experimental in the sqlite source code at 
  >this 
  > > > point.  Thus far I have not found any problems with them.  You can 
  >#define 
  > > > them out on a compilation if you don't want date routines, but they look 
  >to 
  > > > be fully integrated! : - ) 
  > > > 
  > > 
  > > The date and time functions are fully integrated, but they are also still 
  > > "experimental".  They are fully tested with 100% code coverage and will 
  >probably 
  > > work fine.  But they are still "experimental". 
  > > 
  > > "Experimental" means that I reserve the right to change the API in an 
  > > incompatible way in a future release.  The date and time functions will 
  > > stop being experimental once they are in the official documentation and 
  > > we decide to support them in all future releases. 
  > > 
  > > 
  > > -- 
  > > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 
  > > 
  > > 
  > > - 
  > > To unsubscribe, e-mail: [EMAIL PROTECTED] 
  > > For additional commands, e-mail: [EMAIL PROTECTED] 
  > > 
  > 
  > 
  > 
  >- 
  >To unsubscribe, e-mail: [EMAIL PROTECTED] 
  >For additional commands, e-mail: [EMAIL PROTECTED] 
  > 


--
  Make your home warm and cozy this winter with tips from MSN House & Home.