Re: [sqlite] How to truncate the hour fraction
sqlite> select datetime('1201561222', 'unixepoch'); 2008-01-28 23:00:22 OK, so now it's clear your values are Unix times. sqlite> select strftime('%s', date('1201561222', 'unixepoch')); 1201478400 Effectively strips the time portion of your time value sqlite> select datetime('1201478400', 'unixepoch'); 2008-01-28 00:00:00 Proves the result is what you asked for. Good luck! -Clark - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 2:02:09 PM Subject: Re: [sqlite] How to truncate the hour fraction Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ? -> 2008-01-28 00:00:00 Thanks JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:44:07 AM Subject: Re: [sqlite] How to truncate the hour fraction Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ? -> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. Thanks JP - Original Message From: Ken <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:35:03 AM Subject: Re: [sqlite] How to truncate the hour fraction assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTime equivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Joanne Pham wrote: Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 I think your numbers got cutoff... you mean 1201561222 right? Anyway try this: sqlite> SELECT date(1201561222 - (1201561222 % 86400),'unixepoch','localtime'); 2008-01-27 -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 Thanks JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:44:07 AM Subject: Re: [sqlite] How to truncate the hour fraction Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. Thanks JP - Original Message From: Ken <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:35:03 AM Subject: Re: [sqlite] How to truncate the hour fraction assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [sqlite] How to truncate the hour fraction
Joanne Pham wrote: Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ? -> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. So you just want the date (i.e. No time at all)? Seems like you have two options. You can mod against 86400 (number of seconds in a day), or you can just use the date(DateField) option, to only output the date. sqlite> SELECT date('2007-01-19 12:54:32'); 2007-01-19 sqlite> SELECT date('120159','unixepoch','localtime'); 2008-01-28 Lots of good docs here: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ? -> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. Thanks JP - Original Message From: Ken <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:35:03 AM Subject: Re: [sqlite] How to truncate the hour fraction assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [sqlite] How to truncate the hour fraction
assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
[sqlite] How to truncate the hour fraction
Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs