Re: [sqlite] Date comparison on UTC
Andre du Plessis wrote: select date_modified from table where julianday(date_modified) > julianday(CURRENT_TIMESTAMP) - 1; this seems to work perfectly, for some reason the function can simply convert the text successfully, not sure how it's able to do that, but impressive, Is there anyway to round off the datestamp to the beginning of the day, or maybe do you know if there is a list of built-in date functions somewhere? Andre, Check out this page http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions I'm sure it will clear up lots of your questions. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date comparison on UTC
You have an ISO8601 format date. You can expect that to be handled seamlessly. Look up the Sqlite built in date functions. A good place to get it all is to look at date.c. The strftime function is very useful. As I recall the time/date format uses floating point and has the julian day as the integer part and the time as the fraction. You can split them out. Andre du Plessis wrote: select date_modified from table where julianday(date_modified) > julianday(CURRENT_TIMESTAMP) - 1; this seems to work perfectly, for some reason the function can simply convert the text successfully, not sure how it's able to do that, but impressive, Is there anyway to round off the datestamp to the beginning of the day, or maybe do you know if there is a list of built-in date functions somewhere? Thanks. -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 27 August 2007 11:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date comparison on UTC If you use the Sqlite floating point number format (function julianday will do the conversion) you can use functions to compare days and dates. Andre du Plessis wrote: I have a UTC date stored in text column in this format: 2007-08-27 08:58:16.601000 I want to do some selects for all that is newer than one day for example, or then all that is older than 1 month, etc, Any ideas? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date comparison on UTC
select date_modified from table where julianday(date_modified) > julianday(CURRENT_TIMESTAMP) - 1; this seems to work perfectly, for some reason the function can simply convert the text successfully, not sure how it's able to do that, but impressive, Is there anyway to round off the datestamp to the beginning of the day, or maybe do you know if there is a list of built-in date functions somewhere? Thanks. -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 27 August 2007 11:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date comparison on UTC If you use the Sqlite floating point number format (function julianday will do the conversion) you can use functions to compare days and dates. Andre du Plessis wrote: > I have a UTC date stored in text column in this format: > > > > 2007-08-27 08:58:16.601000 > > > > I want to do some selects for all that is newer than one day for > example, or then all that is older than 1 month, etc, > > > > Any ideas? > > > > Thanks. > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date comparison on UTC
If you use the Sqlite floating point number format (function julianday will do the conversion) you can use functions to compare days and dates. Andre du Plessis wrote: I have a UTC date stored in text column in this format: 2007-08-27 08:58:16.601000 I want to do some selects for all that is newer than one day for example, or then all that is older than 1 month, etc, Any ideas? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
