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]
-----------------------------------------------------------------------------
