SQL - How to Get Last Day of the Previous Month Dynamically In This Situation

Question

Good, how do I make to obtain the last day of the previous month dynamically (associated with the current date);

I have a spine which is associated with the date on Milliseconds 'Wo.CREATEDTIME' - Example of some dates in this format :

1527807600000

1143564400000

SELECT Count(wo.WORKORDERID) AS "Request ID",
CASE 
WHEN cd.CATEGORYNAME =  '01 RMS Application' THEN 'Aplicação'
ELSE cd.CATEGORYNAME 
END AS Category
FROM WorkOrder wo 
LEFT JOIN WorkOrderStates wos 
ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd 
ON wos.CATEGORYID=cd.CATEGORYID
 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'
Group By 2
Order By 1 DESC LIMIT 5

In the Part of Where I suppose that’s where we’ll need to make the condition: How do I say Wo.CREATEDTIME <= "Last day of the month Previous" help would be very welcome.

Answer 1

Postgresql has several functions to manipulate dates. First, with date_trunc, you truncate 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, simply 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)

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

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

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

Answer 2

As in your case you need to compare dates in the format of Era UNIX, 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 relative to the current one, you can use the function date_trunc() combined with now(), look at you:

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

Exit:

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

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

To obtain the last time of the previous month, you should just subtract 1 segundo of the date of the first day of the current month:

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

Exit:

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

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

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

Exit:

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

As in your case, you need Era UNIX calculated in millisecond, just multiply everything by 1000, voice:

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

Exit:

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

See working on Sqlfiddle