Postgres SQL

Question

Postgres SQL

komal145

SSCrazy Eights

Points: 9915
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())
)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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 · Answer 2

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!