Обсуждение: .TO_CHAR problem

Поиск
Список
Период
Сортировка

.TO_CHAR problem

От
Guillaume Lémery
Дата:
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 ?

Thanx,

Guillaume.

Re: .TO_CHAR problem

От
Karel Zak
Дата:
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