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

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()));

>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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.

Here is my try :

staging::=> select  to_char(created_at,'DD/MM') || '/' || to_char(now(),'YYYY') as when from users;

    when    

------------

 24/02/2014

...

 20/02/2014

(15 rows)

Can the same be done using any other clever trick ? 

No tricks are springing to mind but a warning is. The above will produce illegal dates whenever you are an inconvenient number of years past February 29. I think this will fix that issue:

select created_at + ((extract(year from now()) - extract(year from created_at)) * '1 year'::interval);

Note that the above returns a date (assuming that created_at is a date). You may need to apply to_char to format to your desired specification.

Cheers,
Steve

On Wednesday, July 02, 2014 08:42:43 AM Steve Crawford wrote:
> On 07/01/2014 11:27 PM, Arup Rakshit wrote:
> > Here is my try :
> >
> > staging::=> select  to_char(created_at,'DD/MM') || '/' ||
> > to_char(now(),'YYYY') as when from users;
> >
> >     when
> >
> > ------------
> >
> >  24/02/2014
> >
> > ...
> >
> >  20/02/2014
> >
> > (15 rows)
> >
> > Can the same be done using any other clever trick ?
>
> No tricks are springing to mind but a warning is. The above will produce
> illegal dates whenever you are an inconvenient number of years past
> February 29. I think this will fix that issue:
>
> select created_at + ((extract(year from now()) - extract(year from
> created_at)) * '1 year'::interval);
>
> Note that the above returns a date (assuming that created_at is a date).
> You may need to apply to_char to format to your desired specification.
>
> Cheers,
> Steve

Thanks Steve. Your warning is 100% valid. *created_at* is a *datetime* data
type.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan