I think your description of 1198990800 is a little off sqlite> select datetime(1198990800, 'unixepoch'); 2007-12-30 05:00:00
To "truncate the hour", as you say: sqlite> select strftime('%s', date(1198990800, 'unixepoch')); 1198972800 Which translates to 2007-12-30 00:00:00 -Clark ----- Original Message ---- From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, December 19, 2007 10:09:42 AM Subject: Re: [sqlite] How to truncate the hour Hi P Sorry for the confusion! Current my hourAppAcclTable is store the following 1, 1198990800 2, 1198998000 which is 2007-12-29 21:00:00 and 2007-12-29 23:00:00 if I used the datetime function to show the time format. What I want is to store this time stamp in different table but truncate all the hour fraction, dayAppAcclTable 1, 1198915200 2, 1198915200 which is 2007-12-29 00:00:00 which GUI run the datetime function Thanks a lot, Joanne ----- Original Message ---- From: P Kishor <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, December 19, 2007 9:49:43 AM Subject: Re: [sqlite] How to truncate the hour 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] ----------------------------------------------------------------------------- ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------