×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Show records NOT BETWEEN date/timestamps

Show records NOT BETWEEN date/timestamps

Show records NOT BETWEEN date/timestamps

(OP)
Hello Everyone,

I have a Postgres database from which I am trying to extract maintenance information. In a nutshell, I am trying to use a subquery to find only the data that does NOT have a corresponding timestamp in the last 90 days.

I have a table that contains a phone number, a table that contains an address, and a table that contains interaction events, with EPOCH timestamps. I would like to find all of the phone numbers which have NOT had an interaction in the last 90 days.

I tried the below, but it did not work. Not sure what the issue is, but this is my first time trying to find something that is "NOT" in a respective time frame.

Thank you.



CODE --> SQL

SELECT CASE LEFT(l.value, 1)
                  WHEN '^' THEN SUBSTR(l.value, STRPOS(l.value,'+'), (LENGTH(l.value)-STRPOS(l.value,'+')+1))
                  ELSE l.value
                  END as value,
                  g.name,
                  i.sourcenumber
FROM   central.phone l INNER JOIN central.address g ON l.locationid=g.id
WHERE  g.name LIKE '%' AND
       l.value IN (SELECT i.phonenumber
                   FROM central.interaction i
                   WHERE i.analyticstime NOT BETWEEN (i.analyticstime >= extract ('epoch' from timestamp '2018-12-12') * 1000 AND
                                                      i.analyticstime < extract ('epoch' from timestamp '2019-02-12') * 1000)
                  )
; 

RE: Show records NOT BETWEEN date/timestamps

Hi

CODE --> PostgreSQL

SELECT CASE LEFT(l.value, 1)
                  WHEN '^' THEN SUBSTR(l.value, STRPOS(l.value,'+'), (LENGTH(l.value)-STRPOS(l.value,'+')+1))
                  ELSE l.value
                  END as value,
                  g.name,
                  i.sourcenumber
FROM   central.phone l INNER JOIN central.address g ON l.locationid=g.id
WHERE  g.name LIKE '%' AND
       l.value IN (SELECT i.phonenumber
                   FROM central.interaction i
--                                                    ,-------------------- evaluates to boolean value ---------------------.
                   WHERE i.analyticstime NOT BETWEEN (i.analyticstime >= extract ('epoch' from timestamp '2018-12-12') * 1000 AND
                                                      i.analyticstime < extract ('epoch' from timestamp '2019-02-12') * 1000)
--                                                    `-------------------- evaluates to boolean value --------------------'
                  )
; 

Got it, right ? There you are comparing an integer against 2 booleans. So the condition should be :

CODE --> PostgreSQL ( fragment )

WHERE i.analyticstime NOT BETWEEN extract ('epoch' from timestamp '2018-12-12') * 1000 AND extract ('epoch' from timestamp '2019-02-12') * 1000 

As I see pretty small chance to also need the calls after 2019-02-12, I would use a simple comparison instead :

CODE --> PostgreSQL ( fragment )

WHERE i.analyticstime < extract ('epoch' from timestamp '2018-12-12') * 1000 

Feherke.
feherke.github.io

RE: Show records NOT BETWEEN date/timestamps

(OP)
feherke,

Thank you for the input. That was exactly my problem!! Your solution worked beautifully.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close