How to dynamically get a specific day in a query

Navigation
1

I would like to get the last day of the previous month in a dynamic way. I do not know how it is that I will by wo.createdtime <= Moth(current_date) something like that and this is in milliseconds, it complicates even more. If anyone could help, thank you. 

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'
    
asked by anonymous 10.07.2018 / 21:58

2 answers

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
    
10.07.2018 / 22:08
0

PostgreSQL has 2 methods for manipulating dates. First, with date_trunc , you truncate the date to have the 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)
    
10.07.2018 / 22:16
Some dynamic way to select IDs with Javascript Redirection after login laravel 5.5