How to convert day from date to date (without timestamp) [PostgreSQL]?

Question

Navigation

#1 by (2 votes)

1

I have the value 2016001 symbolizing the first day of the year 2016, how do I get for example ANO/MES 2016263 ?

Example:

ENTRADA: $data = '2016001'
SAÍDA: $anomes = '2016-01'

If you could do the inverse of this SQL :

SELECT extract (DOY FROM TIMESTAMP '2016-12-31 23:59:59') AS day;

EXIT: 366

So I just extract the number from the day of the year:

SELECT SUBSTRING (TO_CHAR (2016263, '9999999') FROM 6 FOR 3)

EXIT: 263

Or the easy way:

SELECT RIGHT (2016263 :: VARCHAR, 3);

EXIT: 263

date postgresql

asked by anonymous 12.06.2017 / 16:01

json_decode ($ data) PHP [duplicate] How to put only the class name in comboBox?

score 2 · Accepted Answer

A combination of to_date and to_char functions can be used.

The to_date function will convert the varchar you have ( 2016263 ) to a date.

The to_char function will convert this date to the year / month format you need to return ( 2016/09 ).

select to_char(to_date(cast(2016263 as varchar),'yyyyDDD'),'yyyy/mm');

 to_char 
---------
 2016/09
(1 registro)