Postgres/Redshift DATEDIFF convert to FLOAT

datediff redshift
redshift date subtract
postgres datediff
datepart redshift
date_diff athena
redshift current timestamp
redshift interval
date_trunc redshift

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,

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

DATEDIFF function - Amazon Redshift, A date or timestamp columns or expressions that implicitly convert to a date or time stamp. The expressions must both contain the specified date part. If the second  PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision.


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?

CAST and CONVERT functions, Converting null values returns nulls. The expression cannot contain blank or empty strings. Return type. CONVERT returns the data type specified by the type​  Pricing: Redshift vs PostgreSQL. We mentioned this above, but you’re probably also looking for a more detailed breakdown of how Redshift and Postgres compare in terms of pricing. Postgres is a free and open source database package, so Redshift can’t really beat it there, as it’s a paid service. But if you want to set up a Postgres data


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,

Hacking Date Functions in SQLite, Some of the most useful functions in Postgres implementations of SQL (like Amazon Redshift)are DATE_DIFF and DATE_TRUNC : By converting a date to a floating point number, we can use subtraction to find the  Lists the PostgreSQL data types that are not supported in Amazon Redshift. Unsupported PostgreSQL data types - Amazon Redshift AWS Documentation Amazon Redshift Database Developer Guide


SQL Server - DATEDIFF, SQL Server DATEDIFF function returns the difference in seconds, minutes, hours, days, weeks, months, quarters and years between 2 datetime values. GETDATE returns the current date and time in the current session time zone (UTC by default). It returns the start date or time of the current statement, even when it is within a transaction block.


Commonly used Redshift Date Functions and Examples, descriptions and examples of the commonly used Amazon Redshift date functions DATEDIFF (datepart, {date|time},{date|timestamp}), Returns the TO_DATE('current_date', 'format'), Convert string containing date value  Redshift vs. Postgres: The Power of Distributed Data The row-store vs column-store nature of Postgres and Redshift, respectively, is only half the story, though. To manage millions or billions of rows of data, Redshift operates on a cluster with a single leader node and a user-selected number of worker nodes.


Queries for Casting - Amazon Redshift, String to Date. TO_DATE converts a date represented in a character string to a DATE data type. The second argument is a format string Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.