SQL - How to Get Last Day of the Previous Month Dynamically in this Situation

As in your case you need to compare dates in Era UNIX format, you need to get the last second of the previous month and not the last day as you mentioned in the question!

To get the date of the last day of the previous month from the current month, you can use the date_trunc() " combined with now() , see:

SELECT (date_trunc('month', now()) - '1 day'::interval);

Output:

|             ?column? |
|----------------------|
| 2018-06-30T00:00:00Z |

Notice that the output refers to the first moment of the day , that is 00:00:00hrs of the last day of the previous month.

To get the last moment of the previous month , you should subtract only 1 segundo from the date of the first day of the current month:

SELECT (date_trunc('month', now()) - '1 second'::interval);

Output:

|             ?column? |
|----------------------|
| 2018-06-30T23:59:59Z |

Now we can use the date_part() function. to extract from Era UNIX of TIMESTAMP calculated:

SELECT
    date_part( 'epoch', ( date_trunc('month', now()) - '1 SECOND'::INTERVAL) );

Output:

|  date_part |
|------------|
| 1530403199 |

As in your case, you need Era UNIX calculated in milliseconds , suffice multiply all by 1000 , voila :

SELECT
    date_part( 'epoch', ( date_trunc('month', now()) - '1 SECOND'::INTERVAL) ) * 1000;

Output:

|      ?column? |
|---------------|
| 1530403199000 |

See working in SQLFiddle

PostgreSQL has several functions for manipulating dates. First, with date_trunc , you truncate the date to year and the current month. From the documentation:

  

field selects which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

That is, you pass the field you want to truncate ('year', 'month', 'hour', etc.) and it returns you a date with the least significant values zeroed. This gives you the first day of the current month.

But you want the day before the first day of the month. To do this, just subtract 1 day from the truncated date. This subtraction you can do with the command interval , just passing the interval of 1 day.

Then the query is:

postgres=# select (date_trunc('month', now()) - interval '1 day');
    ?column?
------------------------
 2018-06-30 00:00:00-04
(1 row)

But the date is still in date format, not milliseconds. To transform you must use the date_part function, which is responsible for extracting date information.

postgres=# select date_part('epoch', date_trunc('month', now()));
   ?column?
---------------
 1530417600
(1 row)

Returning the time in seconds. For milliseconds, simply multiply by 1000;

Your query with WHERE would look something like this (simplified version):

SELECT Count(wo.WORKORDERID) AS "Request ID",
...
FROM WorkOrder wo
...
WHERE wo.CREATEDTIME <= date_part('epoch', date_trunc('month', now())) * 1000;