Postgres SQL

• komal145

  SSCrazy Eights

  Points: 9918

  More actions

  July 31, 2017 at 2:57 pm

  #343788

  I have to connect to source PostgreSQL and pull some data. Thing is i did not work on PostgreSQL before.
  I was trying to  do same as tsql and realized i  was doing wrong. Can anyone help me re-write TSQl query to PostgreSQL?

  Trying to get the yesterday data from morning till midnight. Something like below.

  WHERE
  (
  (lastupdateddate > DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) -1, 0)
  AND lastupdateddate <= DATEADD(DAY, 0, GETDATE())
  )

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  July 31, 2017 at 6:47 pm

  #1953498

  There are some people on this site who work with PostgreSQL, but you'll probably get a quicker response by posting your question to a PostgreSQL forum.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 1, 2017 at 6:45 am

  #1953570

  I don't have a psotgres linked server any more, but the key was how adding or subtracting to a date is different.
  CURRENT_TIMESTAMP and CURRENT_DATE give you GETDATE() or getdate() with no time.
  Adding and subtracting time uses the + INTERVAl statement
  ie
  SELECT CURRENT_DATE + INTERVAL '1 day';

  so i *think* you are looking for something like this:
  
WHERE 
(
(lastupdateddate > (CURRENT_DATE - INTERVAL '1 day')
AND lastupdateddate <= CURRENT_DATE 
) 

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!