Issue in postgresql query

Question

i have following query.But TranDate,Amount,Balance are displaying Null. Am new to postgresql. But in sql server values are displaying

CREATE TEMP TABLE tran
  (TranDate,Amount,Balance) AS
VALUES 
  ('2019-01-01'::date, 1000::int,1000::int), 
  ('2019-01-02', 2000,3000), 
  ('2019-01-03', NULL,3000),
  ('2019-01-04', -500,2500);


  SELECT tran.TranDate,tran.Amount,tran.Balance, date(d) as day
         FROM   generate_series(timestamp '2018-01-01'
                     , timestamp '2018-01-31'
                     , interval  '1 day') d  
             left join  tran  ON  date(tran.TranDate) = d

You are comparing dates in 2019 with timestamps in 2018. No match is expected. — Gordon Linoff
– Gordon Linoff, Commented Apr 1, 2019 at 16:24

Gordon Linoff · Accepted Answer · 2019-04-01 16:25:32Z

1

If you run the code using overlapping date ranges, you will see results:

SELECT tran.TranDate, tran.Amount, tran.Balance, date(d) as day
FROM generate_series(timestamp '2019-01-01',
                     timestamp '2019-01-31',
                     interval  '1 day'
                    ) d left join
     tran 
     ON  date(tran.TranDate) = d ;

You can observe this in this db<>fiddle.

answered Apr 1, 2019 at 16:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Lajith Over a year ago

Thanks ..am confused date time in postgresql..i thought different issue..

Collectives™ on Stack Overflow

Issue in postgresql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related