CODEWITHSUNDEEP
sqlamazon-redshift
PR102012
PR102012

Reputation: 878

Postgres/Redshift DATEDIFF convert to FLOAT

I am using Python SQLAlchemy to connect to a Redshift instance. I am trying to calculate a DATEDIFF in minutes between 2 datetimes, which works. But when I try to CAST the result to FLOAT and use it in a CASE WHEN against ' < 1' , I get only 'THEN' results?

SELECT distinct u2.id,
  CASE 
    WHEN 
      CAST(DATEDIFF(minute, u1.usa_start_datetime::timestamp,u1.usa_end_datetime::timestamp) * 1.00 as FLOAT) < 1.00 
      THEN 1.00 END as fsess_hr,

Upvotes: 0

Views: 2248

Answers (3)

Ben Squire
Ben Squire

Reputation: 155

I find that ::float works with datediff just fine. Looks cleaner than cast() as float

SELECT distinct u2.id,
CASE 
    WHEN DATEDIFF(minute,u1.usa_start_datetime::timestamp,u1.usa_end_datetime::timestamp)::float < 1.00 
THEN 1.00 END as fsess_hr,

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35573

The return type of DATEDIFF() is BIGINT and you have chosen to use minutes as the unit. So deal with the integers within the case expression then cast the value to float

SELECT /* DISTINCT */ -- is this really needed? 
      u2.id
    , CAST(
            CASE WHEN DATEDIFF(MINUTE, u1.usa_start_datetime ::timestamp, u1.usa_end_datetime ::timestamp)  < 1 THEN 1
                 ELSE DATEDIFF(MINUTE, u1.usa_start_datetime ::timestamp, u1.usa_end_datetime ::timestamp)
            END 
      AS Float) AS fsess_hr
FROM ...

BUT you are still returning a number of minutes, so I don't follow why it is called fsess_hr, do you divide it by 60.0 at some point?

Upvotes: 1

pault
pault

Reputation: 43494

You are missing an ELSE clause in your CASE statement. If you want to get 1.0 if the difference is less than 1 and the difference as a floating point number otherwise, modify your query as follows:

SELECT distinct 
       u2.id,
       CASE 
         WHEN CAST(DATEDIFF(minute, u1.usa_start_datetime::timestamp,u1.usa_end_datetime::timestamp) * 1.00 as FLOAT) < 1.00 THEN 1.00 
         ELSE CAST(DATEDIFF(minute, u1.usa_start_datetime::timestamp,u1.usa_end_datetime::timestamp) * 1.00 as FLOAT) 
       END as fsess_hr
  FROM some_table

Also, I don't think you need the * 1.00

Upvotes: 1

Related Questions