Search Postgresql Archives

Re: How can I replace the year of the created_at column with the current year dynamically ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Adrian Klaver-4 wrote
> On 07/02/2014 12:48 AM, Arup Rakshit wrote:
>>
> 
>>
>> What is the data at your disposal when trying to select the current
>> year? If it is a timestamp, simply use date_part:
>> =# select date_part('year', now());
>>   date_part
>>
>> -----------
>>        2014
>>
>> (1 row)
>> --
>> Michael
>>
>> It is *datetime*. Now my users are created at different date...
>>
>> say -
>>
>> user1   24/02/1997
>> user2 28/02/2011
>> user3 02/03/2001
>> user4 01/03/2003
>> .....
>>
>> But I have some requirment, where date/month part will be as it is...
>> but as per the current year, I will replace the actual year with the
>> current year, while I will be displaying it. To meet this need, I am
>> currently doing as
>>
>> select  to_char(created_at,'DD/MM') || '/' || to_char(now(),'YYYY') as
>> when from users;
> 
> 
> Maybe simplify it a bit:
> 
> select to_char('2011-01-01'::timestamp,'DD/MM/' || to_char(now(),'YYYY'));
> 
> or per Michaels suggestion:
> 
> select to_char('2011-01-01'::timestamp,'DD/MM/' || date_part('year', 
> now()));

[not syntactically correct]

ALTER TABLE ... ADD COLUMN created_at_monthday_prefix text --stores 'MM/DD/'
CREATE FUNCTION current_year() RETURNS text AS ...; --return YYYY

SELECT created_at_monthday_prefix || current_year();

OR even

CREATE FUNCTION day_in_current_year(source_date date) RETURNING date/text...

SELECT day_in_current_year(created_at);

The only way to actually calculate the new date is to, at some point, break
apart the existing date and then join the m/d component back with today's
year - which has multiple likely nearly identical solutions.  My suggestions
is to wrap that in user functions and, in the first case, cache the result
of pulling out the m/d component so you do not have to do so repeatedly.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-can-I-replace-the-year-of-the-created-at-column-with-the-current-year-dynamically-tp5810122p5810192.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux