3 Replies Latest reply on Oct 13, 2016 3:59 PM by Dmitry Chirkov

    Postgresql

    Angelo Canepa

      Hello,

       

      I having a problem with Postgres and time data type.

      I'm working with Tableau and I'm trying to convert the column called start_at_minute, where it says the hours that a particular schedule start to run, the problems that this column is in minutes so for example if the schedule starts at 8:15 there is 495 minutes.

       

      I have the next formula

      cast(to_timestamp(concat((start_at_minute/60),':',(cast(start_at_minute as float)/60-(start_at_minute/60))*60),'HH24 MI') as TIME) as scheduletime

       

      But the problem is that Tableau after executing the query launches an error, 'Unrecognised Error'

       

      PS: I'm using Tableau 9.0

       

      Regards,

        • 1. Re: Postgresql
          Dmitry Chirkov

          You use this formula in a bigger Custom SQL statement, right?

          Can you share that?

           

          Also, are there extra buttons on error dialog like 'Show Query' or 'Show Details'?

           

          I can run this query with no issues:

          select cast(to_timestamp(concat((495/60),':',(cast(495 as float)/60-(495/60))*60),'HH24 MI') as TIME) as scheduletime
          
          • 2. Re: Postgresql
            Angelo Canepa

            The custom query that I want to run is this:

            select

             

             

            s.name as Site,

            e.name as Project,

            c.name as Workbook_Name,

            a.name as Schedule_Name,

            case when a.day_of_week_mask=62 then 'Monday to Friday'

            when a.day_of_week_mask=64 then 'Saturday'

            when a.day_of_week_mask =2  then 'Monday'

            when a.day_of_week_mask=0 then 'All Week'

            else null end as Days_Week,

            cast(to_timestamp(concat((start_at_minute/60),':',(cast(start_at_minute as float)/60-(start_at_minute/60))*60),'HH24 MI') as TIME) as scheduletime,

            run_next_at as Next_Run_sCHEDULE,

            c.created_at as created_workbook,

            c.updated_at as last_update_workbook

             

             

            from schedules a

            inner join tasks b

            on a.id = b.schedule_id

            inner join workbooks c

            on b.obj_id = c.id

             

             

            left join hist_sites s

            on c.site_id = s.site_id

             

             

            left join projects e

            on c.project_id = e.id

             

             

            order by c.created_at desc

             

            If I delete the cast conversion the query runs.

            • 3. Re: Postgresql
              Dmitry Chirkov

              Two things that are incorrect in your calculation

              1. You are passing decimal number as minutes (i.e. "6:18.999999999999986")
              2. You are concatenating with ":" and yet in in format mask you are using " "

               

              Use this:

              cast(to_timestamp(concat(DIV(start_at_minute, 60),':',MOD(start_at_minute, 60)),'HH24:MI') as TIME) as scheduletime