How to convert this T-SQL code to PostgreSQL / plpgsql

Question

I need to convert the following tsql function code into a plpgsql function and I have absolutely no idea how:

BEGIN
    DECLARE @StartDate DATETIME
    DECLARE @ResultDate DATETIME

    SET @StartDate = CONVERT(DATETIME, 0)

    SET @ResultDate = 
    CASE @Type
        WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
        WHEN 1 THEN DATEADD(mi, CEILING(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
        ELSE @Date
END

RETURN @ResultDate

Here is the fullquote:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetIntervalDate] 

(
    @Date DATETIME,
    @Type INT,
    @Interval INT
)  
RETURNS DATETIME

AS

BEGIN

DECLARE @StartDate DATETIME

DECLARE @ResultDate DATETIME

    SET @StartDate = CONVERT(DATETIME, 0)

    SET @ResultDate = 
    CASE @Type
        WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
        WHEN 1 THEN DATEADD(mi, CEILING(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
        ELSE @Date
    END

    RETURN @ResultDate
END

This appears to be a function. What's the name of the function, and are @Date, @Interval, and @Type parameters to the function? It would be helpful to see the function declaration. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 29, 2010 at 11:00

Erwin Brandstetter · Accepted Answer · 2011-10-21 21:54:11Z

3

Got it:

CREATE FUNCTION get_interval_date("@Date" timestamp, "@Type" int, "@Interval" int)
    RETURNS timestamp with time zone AS
$BODY$
DECLARE
    _mystamp timestamp;
    _round_secs decimal;
BEGIN
    _round_secs := "@Interval"::decimal;

    IF "@Type" = 0 THEN
        RETURN timestamptz 'epoch'
             + FLOOR((EXTRACT(EPOCH FROM "@Date"))::int / _round_secs) * _round_secs * INTERVAL '1 second';
    ELSIF "@Type" = 1 THEN  
        RETURN timestamptz 'epoch'
             + CEIL((EXTRACT(EPOCH FROM "@Date"))::int / _round_secs) * _round_secs * INTERVAL '1 second';
    ELSE
        RETURN "@Date";
    END IF;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE;

Maybe anyone else needs something like this.

edited Oct 21, 2011 at 21:54

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

answered Jul 29, 2010 at 12:32

dbu

311 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rfusca Over a year ago

You only need those double quotes around it all because you did the whole @Uppercase thing. Drop the case to lower and drop the quotes - its alot more normal plpgsql then.

Erwin Brandstetter · Accepted Answer · 2014-02-25 21:34:02Z

I translated your function to modern "native" plpgsql:

CREATE OR REPLACE FUNCTION get_interval_date(_ts timestamp
                                            ,_type int
                                            ,_interval int)
  RETURNS timestamp AS
$func$
BEGIN
   CASE _type 
   WHEN 0 THEN
      RETURN timestamp 'epoch'
           + floor((extract(EPOCH FROM _ts)) / _interval)
           * _interval * interval '1s';
   WHEN 1 THEN
      RETURN timestamp 'epoch'
           + ceil((extract(EPOCH FROM _ts)) / _interval)
           * _interval * interval '1s';
   ELSE
      RETURN _ts;
   END CASE;
END
$func$ LANGUAGE plpgsql IMMUTABLE;

Major points

datetime maps to timestamp, not timestamp with timezone
Use lower case parameter names instead of quoted mixed case T-SQL style parameter syntax.
Remove pointless cast of _interval to decimal (doesn't change result).
Remove unused _mystamp.
Remove therefore unused DECLARE.
Use better suited CASE instead of IF.
Never quote the language name plpgsql. It's an identifier, not a string; tolerated for now, but may lead to problems.

Collectives™ on Stack Overflow

How to convert this T-SQL code to PostgreSQL / plpgsql

2 Answers 2

1 Comment

Major points

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Major points

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related