Re: [sqlite] How to truncate the hour fraction

2008-01-23 Thread Clark Christensen 
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

2008-01-23 Thread Scott Baker 

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

2008-01-23 Thread Joanne Pham 
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

2008-01-23 Thread Scott Baker 

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

2008-01-23 Thread Joanne Pham 
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

2008-01-23 Thread Ken 
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

2008-01-23 Thread Joanne Pham 
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

Re: [sqlite] How to truncate the hour

2007-12-19 Thread Clark Christensen 
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]
-

Re: [sqlite] How to truncate the hour

2007-12-19 Thread Scott Baker 
Joanne Pham wrote:
> 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

If you're dealing with dates or times I always store the full
unixtime (like you are) and then use the functions date, time, and
datetime to get the parts you need. That way you have the most
functionality as well as requiring the least amount of storage space
(4 bytes for a unixtime).

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to truncate the hour

2007-12-19 Thread Joanne Pham 
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

Re: [sqlite] How to truncate the hour

2007-12-19 Thread P Kishor 
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]
-

Re: [sqlite] How to truncate the hour

2007-12-19 Thread Clark Christensen 
select date(startTime, 'unixepoch','-8 hours');

SQLite's date/time functions are documented at 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

 -Clark

- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, December 19, 2007 9:22:39 AM
Subject: [sqlite] How to truncate the hour


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.
Thanks in advance for your help,
Joanne



  

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

2007-12-19 Thread Joanne Pham 
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




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


  

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

2007-12-19 Thread P Kishor 
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]
-

[sqlite] How to truncate the hour

2007-12-19 Thread Joanne Pham 
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.
Thanks in advance for your help,
Joanne


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs