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.
