How to dynamically get specific day in a query

Question

I would like to get the last day of the previous month in a dynamic way. I don’t know how to put the wo.createdtime <= Moth(current_date) something of the kind and this is in milliseconds, complicates even more. If someone can help, would appreciate.

SELECT wo.WORKORDERID AS "Request ID",   
  FROM WorkOrder wo   
WHERE > (((wo.CREATEDTIME >= 1527807600000)  
 AND ((wo.CREATEDTIME != 0) 
 AND (wo.CREATEDTIME IS NOT NULL))) 
 AND ((wo.CREATEDTIME <= 1530399599000) 
 AND (((wo.CREATEDTIME != 0)
 AND (wo.CREATEDTIME IS NOT NULL))   
 AND (wo.CREATEDTIME != -1))))   
 AND wo.ISPARENT='1'
 AND wo.IS_CATALOG_TEMPLATE='0'

Answer 1

SELECT (date_trunc
        ('month',  current_date - interval '1' month) 
        + 
        interval '1 month' - interval '1 day')::date
AS ultimo_dia_mes_anterior;

Today, the query returns:

2018-06-30

Answer 2

Postgresql has 2 methods to manipulate dates. First, with date_trunc, Voce truncates the date to have the current year and month. From the documentation:

field selects to 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).

IE, you pass the field you want to truncate ('year', 'Month,'hour', etc.) and it returns to you a date with the least significant values zeroed. With this you can have 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 1 day break.

That is how the consultation:

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