On Thu, 15 Feb 2001, Guillaume [ISO-8859-1] Lémery wrote:
> Hi all,
>
> I want to have some stats from my data grouped by 2hours periodes.
>
> Here is the query :
>
> SELECT TO_DATE(TO_CHAR (date_clic,'DD-MM-YYYY ')
> || (TO_CHAR(
> TO_NUMBER(
> TO_CHAR(date_clic,'hh24')
> , 99)
> - mod(
> TO_NUMBER(
> TO_CHAR(date_clic,'hh24')
> , 99)
> ,2)
> , 'hh24')
> )
> ||':00','DD-MM-YYYY HH24:MI') AS date_debut
> FROM clic_campagne;
>
> But I only get the date whithout the hours and the minute, how can I get
> them ?
As you say "..get the date...", hours+minutes+ contains timestamp.
You must call SELECT TO_TIMESTAMP(...) instead TO_DATE(...).
BTW, I not sure if your TO_NUMBER() is needful, less expensive will
probably TO_CHAR(date_clic,'hh24')::int.
Karel