Re: [web2py] Re: web2py dal week query

2014-03-14 Thread 黄祥 
a, i c, thank you so much for your hints, michele.

best regards,
stifan

On Friday, March 14, 2014 6:41:11 AM UTC+7, Michele Comitini wrote:

 From postgresql manual: 

 http://www.postgresql.org/docs/current/static/functions-datetime.html 


 SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); 

 Not supported by DAL expressions, but you can use executesql... 

 2014-03-14 0:26 GMT+01:00 黄祥 steve.van...@gmail.com javascript:: 
  actually i wan to create a chart from that query : 
  e.g. for year and month is work this way 
  views/default/index.html 
  {{sum_sale_order = db.sale_order_header.total.sum() }} 
  {{query_yearly_sale_order = (db.sale_order_header.sale_order_date.year() 
 == 
  request.now.year) }} 
  {{sum_yearly_sale_order = 
  
 db(query_yearly_sale_order).select(sum_sale_order).first()[sum_sale_order] 
  if db(query_yearly_sale_order).select() else 0}} 
  
  but when it goes to week, it return and error. and pardon, i don't think 
  this is related with database engine, because in php it can be achieved. 
  e.g. OpenCart 
  
  doing some test that work is : 
  {{import datetime}} 
  {{=datetime.date.today().isocalendar()[1]}} 
  {{=request.now.isocalendar()[1]}} 
  
  all return the same week number : 11 
  
  but i can't get it work when i passed the datetime field value into week 
 to 
  compare with the value above. 
  
  best regards, 
  stifan 
  
  -- 
  Resources: 
  - http://web2py.com 
  - http://web2py.com/book (Documentation) 
  - http://github.com/web2py/web2py (Source code) 
  - https://code.google.com/p/web2py/issues/list (Report Issues) 
  --- 
  You received this message because you are subscribed to the Google 
 Groups 
  web2py-users group. 
  To unsubscribe from this group and stop receiving emails from it, send 
 an 
  email to web2py+un...@googlegroups.com javascript:. 
  For more options, visit https://groups.google.com/d/optout. 


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: web2py dal week query

2014-03-13 Thread Massimo Di Pierro 
No because the database engines do not support week(..). Are you looking 
for something like all Tuesdays?

On Thursday, 13 March 2014 11:16:49 UTC-5, 黄祥 wrote:

 hi,

 i'm interest in web2py dal query about year, month, day, hour, minutes, 
 seconds
 but i can't see for week query. is it possible to do the query for the 
 week?
 e.g. this work for year and month
 (db.sale_order_header.sale_order_date.year() == request.now.year)
 (db.sale_order_header.sale_order_date.month() == request.now.month)

 but not for week
 (db.sale_order_header.sale_order_date.week() == request.now.week)

 thanks and best regards,
 stifan

 ref :

 http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#year--month--day--hour--minutes--seconds


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: web2py dal week query

2014-03-13 Thread 黄祥 
pardon, not sure what do you mean with all Tuesdays. what i want to achieve 
is to get week number, something like in python :
isocalendar()
strftime(%U)

ref:
http://stackoverflow.com/questions/2600775/how-to-get-week-number-in-python

how to achieve it using web2py method?

thanks and best regards,
stifan

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread Richard Vézina 
Did you try what suggested in the ref you post? If it works with datetime
it should work with web2py date, since it a datetime object...

But maybe you want something else like Python Calendar... Hard to say with
knowing what you want to do...

Richard


On Thu, Mar 13, 2014 at 1:14 PM, 黄祥 steve.van.chris...@gmail.com wrote:

 pardon, not sure what do you mean with all Tuesdays. what i want to
 achieve is to get week number, something like in python :
 isocalendar()
 strftime(%U)

 ref:
 http://stackoverflow.com/questions/2600775/how-to-get-week-number-in-python

 how to achieve it using web2py method?

 thanks and best regards,
 stifan

 --
 Resources:
 - http://web2py.com
 - http://web2py.com/book (Documentation)
 - http://github.com/web2py/web2py (Source code)
 - https://code.google.com/p/web2py/issues/list (Report Issues)
 ---
 You received this message because you are subscribed to the Google Groups
 web2py-users group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to web2py+unsubscr...@googlegroups.com.
 For more options, visit https://groups.google.com/d/optout.


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread 黄祥 
yes, already tried but got an error traceback. what i want to achieve is 
something like example below, but when i've tested it, i got an error 
traceback. any idea how to achieve it in web2py?
e.g.
import datetime

(db.sale_order_header.sale_order_date.strftime(%U) == 
request.now.strftime(%U) )
*AttributeError: 'Field' object has no attribute 'strftime'*

(db.sale_order_header.sale_order_date.isocalendar() == 
request.now.isocalendar() )
(db.sale_order_header.sale_order_date.isocalendar()[1] == 
request.now.isocalendar()[1] )
*AttributeError: 'Field' object has no attribute 'isocalendar'*

best regards,
stifan

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread Richard Vézina 
I see you want to make a query but why you want to make that?

Also, more experienced user correct me if I am wrong, but personnally I
never had good result doing what you are doing, I mean I don't remember
having it working correctly (even in SQL)...

In :
(db.sale_order_header.sale_order_date.isocalendar() ==
request.now.isocalendar() )
You say transform my field value before checking if it equal to some value
db.sale_order_header.sale_order_date.isocalendar()

How dow you want the DAL translate that into SQL?

Did you have a look at the SQL generated by the DAL?

(db.sale_order_header.sale_order_date.isocalendar() ==
request.now.isocalendar() ).*_*select(...)

Richard



On Thu, Mar 13, 2014 at 1:59 PM, 黄祥 steve.van.chris...@gmail.com wrote:

 yes, already tried but got an error traceback. what i want to achieve is
 something like example below, but when i've tested it, i got an error
 traceback. any idea how to achieve it in web2py?
 e.g.
 import datetime

 (db.sale_order_header.sale_order_date.strftime(%U) ==
 request.now.strftime(%U) )
 *AttributeError: 'Field' object has no attribute 'strftime'*

 (db.sale_order_header.sale_order_date.isocalendar() ==
 request.now.isocalendar() )
 (db.sale_order_header.sale_order_date.isocalendar()[1] ==
 request.now.isocalendar()[1] )
 *AttributeError: 'Field' object has no attribute 'isocalendar'*

 best regards,
 stifan

 --
 Resources:
 - http://web2py.com
 - http://web2py.com/book (Documentation)
 - http://github.com/web2py/web2py (Source code)
 - https://code.google.com/p/web2py/issues/list (Report Issues)
 ---
 You received this message because you are subscribed to the Google Groups
 web2py-users group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to web2py+unsubscr...@googlegroups.com.
 For more options, visit https://groups.google.com/d/optout.


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread Richard Vézina 
db.table(1).field.isocalendar()[1] will works because you have a record
selected...

But you don't have access to any field value in context of where clause
db(** where clause **).select()

Richard


On Thu, Mar 13, 2014 at 2:17 PM, Richard Vézina ml.richard.vez...@gmail.com
 wrote:

 I see you want to make a query but why you want to make that?

 Also, more experienced user correct me if I am wrong, but personnally I
 never had good result doing what you are doing, I mean I don't remember
 having it working correctly (even in SQL)...

 In :
 (db.sale_order_header.sale_order_date.isocalendar() ==
 request.now.isocalendar() )
 You say transform my field value before checking if it equal to some value
 db.sale_order_header.sale_order_date.isocalendar()

 How dow you want the DAL translate that into SQL?

 Did you have a look at the SQL generated by the DAL?

 (db.sale_order_header.sale_order_date.isocalendar() ==
 request.now.isocalendar() ).*_*select(...)

 Richard



 On Thu, Mar 13, 2014 at 1:59 PM, 黄祥 steve.van.chris...@gmail.com wrote:

 yes, already tried but got an error traceback. what i want to achieve is
 something like example below, but when i've tested it, i got an error
 traceback. any idea how to achieve it in web2py?
 e.g.
 import datetime

 (db.sale_order_header.sale_order_date.strftime(%U) ==
 request.now.strftime(%U) )
 *AttributeError: 'Field' object has no attribute 'strftime'*

 (db.sale_order_header.sale_order_date.isocalendar() ==
 request.now.isocalendar() )
 (db.sale_order_header.sale_order_date.isocalendar()[1] ==
 request.now.isocalendar()[1] )
 *AttributeError: 'Field' object has no attribute 'isocalendar'*

 best regards,
 stifan

 --
 Resources:
 - http://web2py.com
 - http://web2py.com/book (Documentation)
 - http://github.com/web2py/web2py (Source code)
 - https://code.google.com/p/web2py/issues/list (Report Issues)
 ---
 You received this message because you are subscribed to the Google Groups
 web2py-users group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to web2py+unsubscr...@googlegroups.com.
 For more options, visit https://groups.google.com/d/optout.




-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread 黄祥 
actually i wan to create a chart from that query :
e.g. for year and month is work this way 
*views/default/index.html*
{{sum_sale_order = db.sale_order_header.total.sum() }}
{{query_yearly_sale_order = (db.sale_order_header.sale_order_date.year() == 
request.now.year) }}
{{sum_yearly_sale_order = 
db(query_yearly_sale_order).select(sum_sale_order).first()[sum_sale_order] 
if db(query_yearly_sale_order).select() else 0}}

but when it goes to week, it return and error. and pardon, i don't think 
this is related with database engine, because in php it can be achieved. 
e.g. OpenCart

doing some test that work is :
{{import datetime}}
{{=datetime.date.today().isocalendar()[1]}}
{{=request.now.isocalendar()[1]}}

all return the same week number : 11

but i can't get it work when i passed the datetime field value into week to 
compare with the value above.

best regards,
stifan

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: web2py dal week query

2014-03-13 Thread Michele Comitini 
From postgresql manual:

http://www.postgresql.org/docs/current/static/functions-datetime.html


SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

Not supported by DAL expressions, but you can use executesql...

2014-03-14 0:26 GMT+01:00 黄祥 steve.van.chris...@gmail.com:
 actually i wan to create a chart from that query :
 e.g. for year and month is work this way
 views/default/index.html
 {{sum_sale_order = db.sale_order_header.total.sum() }}
 {{query_yearly_sale_order = (db.sale_order_header.sale_order_date.year() ==
 request.now.year) }}
 {{sum_yearly_sale_order =
 db(query_yearly_sale_order).select(sum_sale_order).first()[sum_sale_order]
 if db(query_yearly_sale_order).select() else 0}}

 but when it goes to week, it return and error. and pardon, i don't think
 this is related with database engine, because in php it can be achieved.
 e.g. OpenCart

 doing some test that work is :
 {{import datetime}}
 {{=datetime.date.today().isocalendar()[1]}}
 {{=request.now.isocalendar()[1]}}

 all return the same week number : 11

 but i can't get it work when i passed the datetime field value into week to
 compare with the value above.

 best regards,
 stifan

 --
 Resources:
 - http://web2py.com
 - http://web2py.com/book (Documentation)
 - http://github.com/web2py/web2py (Source code)
 - https://code.google.com/p/web2py/issues/list (Report Issues)
 ---
 You received this message because you are subscribed to the Google Groups
 web2py-users group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to web2py+unsubscr...@googlegroups.com.
 For more options, visit https://groups.google.com/d/optout.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.