Show records NOT BETWEEN date/timestamps

[MCP2000]

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.

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)
                  )
;

 

[feherke]

Hi

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

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

[b]WHERE[/b] i[teal].[/teal]analyticstime [b]NOT BETWEEN[/b] extract [teal]([/teal][i][green]'epoch'[/green][/i] [b]from[/b] [maroon]timestamp[/maroon] [i][green]'2018-12-12'[/green][/i][teal]) *[/teal] [purple]1000[/purple] [b]AND[/b] extract [teal]([/teal][i][green]'epoch'[/green][/i] [b]from[/b] [maroon]timestamp[/maroon] [i][green]'2019-02-12'[/green][/i][teal]) *[/teal] [purple]1000[/purple]

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

[b]WHERE[/b] i[teal].[/teal]analyticstime [teal]<[/teal] extract [teal]([/teal][i][green]'epoch'[/green][/i] [b]from[/b] [maroon]timestamp[/maroon] [i][green]'2018-12-12'[/green][/i][teal]) *[/teal] [purple]1000[/purple]

Feherke.
feherke.github.io

 

[MCP2000]

feherke,

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