On 12/19/07, Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi, > strftime doesn't work for me. I appreciate if you can help me on this. > > My hourlyAppAcclTable > row1 - 1, 1198990800 -- (which is 12-29-2007 21:00:00) > row 2 - 2, 1198998000 - (which is 12-29-2007 23:00:00) > I want to truncate all the hour fraction and store these time in > dayAppAcclTable > 1, 1198915200 ( 12-29-2007 00:00:00) > 2, 1198915200 (12-29-2007 00:00:00) > I still want to store the GMT time in my dayAppAcclTable as INTEGER too. > Thanks, > Joanne >
I am not really sure what you want to do, nor can I figure out what exactly you have tried to do. Did you try sqlite> select date(1198990800, 'unixepoch', '-8 hours'); 2007-12-29 is that what you want when you mean "truncate the hour"? Please read the datetime wiki at http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions carefully. Most of your questions will be answered there. > > > > ----- Original Message ---- > From: P Kishor <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Sent: Wednesday, December 19, 2007 9:28:22 AM > Subject: Re: [sqlite] How to truncate the hour > > On 12/19/07, Joanne Pham <[EMAIL PROTECTED]> wrote: > > Hi All, > > I have the table as defined below: > > hourlyAppAcclTable ( appid INTEGER, > > startTime INTEGER -- is number of seconds in > > GMT time in the integer > > ............. > > ) > > > > the values in this table is : > > row1 - 1, 1198990800 > > row 2 - 2, 1198998000 > > If I ran the following sql statement: > > select datetime(startTime, 'unixepoch','-8 hours'); > > I got the output as below: > > 12-29-2007 21:00:00 > > 12-29-2007 23:00:00 > > I want to store this information in different table, dayAppAcclTable but > > the startTime is the day. So I want to truncate all the hour. > > 1, 1198915200 ( 12-29-2007 00:00:00) > > 2, 1198915200 (12-29-2007 00:00:00) > > So I want to have generic the formular/function to truncate all hours and > > only keep the day. > > > did you check out the formatting functions on the datetime wiki? Check > out strftime > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------