change time function from oracle to postgresql

Question

i have got problem with transfer 2 data function from oracle to postgresql can you help me:

Data:

INTERVALL = numeric(10,0)

START_DATE = timestamp without time zone

REPETITIONS = numeric(10,0)

This expression i copy from case state. I must write equivalent logical action in postgresql.

 case when (extract(DAY FROM (START_DATE + TRUNC(INTERVALL*(REPETITIONS-1) * 7)))
   - extract(DAY FROM CURRENT_TIMESTAMP)) >= 0 then 'OK'

and second:

case when (extract(DAY FROM add_months(START_DATE, TRUNC(INTERVALL*
  REPETITIONS-1) * 12))) - extract(DAY FROM CURRENT_TIMESTAMP)) <= 0 then 'ok'

Please, in order to avoid guessing games, describe your tables and provide better description of what you need to achieve. — vyegorov
– vyegorov, Commented Feb 21, 2013 at 21:46
I think that for 1 this is good: SELECT extract(DAY FROM (CURRENT_DATE + interval '1' day * TRUNC(7 *(2-1)))) — Łukasz Woźniczka
– Łukasz Woźniczka, Commented Feb 21, 2013 at 22:55
I think comparing days is wrong and it is also wrong in Oracle. You would have better results if you explained your logic. — Clodoaldo Neto
– Clodoaldo Neto, Commented Feb 21, 2013 at 23:02

Clodoaldo Neto · Accepted Answer · 2013-02-21 22:53:18Z

1

case when (
    extract(DAY FROM (START_DATE + INTERVALL * (REPETITIONS - 1) * interval '1 week'))
    >=
    extract(DAY FROM CURRENT_TIMESTAMP))
then 'OK'

case when (
    extract(DAY FROM (START_DATE + INTERVALL * (REPETITIONS - 1) * interval '1 year')))
    <= extract(DAY FROM CURRENT_TIMESTAMP))
then 'ok'

edited Feb 21, 2013 at 22:53

answered Feb 21, 2013 at 22:47

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

Łukasz Woźniczka Over a year ago

Why you use * interval '1 week' ? not day ?

vyegorov Over a year ago

@ŁukaszWoźniczka, it would be much easier if you could explain logic of your calculations. For me 1 week interval here looks natural.

Clodoaldo Neto Over a year ago

@ŁukaszWoźniczka You are multiplying by 7 so I suppose you want weeks.

vyegorov · Accepted Answer · 2013-02-21 22:53:06Z

0

PostgreSQL has way better support of date, time and interval types compared to ORACLE.

I guess (sorry, but you haven't described this), that you need to compare timestamp column with the current_timestamp based on the interval, that is calculated from columns INTERVALL and REPETITIONS. I also think that in the first case you have something to do with week intervals, and months in the second snippet. (I don't fully understand the logic behind TRUNC(…) parts of your code.

I recommend the following:

rename start_date, removing _date suffix, as column's type is timestamp in fact, misleading;
convert intervall to text and store interval type directly, like month or week;
keep repetitions as is, defaulting to 1 for NULL values;
PostgreSQL supports boolean type natively, so you don't really need the CASE … END construct here, depends on the Application though.

Therefore the following query can do it (also on SQL Fiddle):

SELECT start_dt, intervall, repetition,
       start_dt + (repetition||intervall)::interval
         >= current_timestamp AS is_it_ok
  FROM tab;

answered Feb 21, 2013 at 22:53

vyegorov

23k7 gold badges61 silver badges76 bronze badges

1 Comment

Łukasz Woźniczka Over a year ago

Its not my code :) I am only migration person :P Java developer not database admin ....

Collectives™ on Stack Overflow

change time function from oracle to postgresql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related