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

On Wed, Jul 2, 2014 at 3:27 PM, Arup Rakshit <aruprakshit@rocketmail.com>
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
28/02/2014
02/03/2014
01/03/2014
04/03/2014
02/03/2014
06/03/2014
07/05/2014
02/06/2014
06/06/2014
20/02/2014
20/02/2014
20/02/2014
20/06/2014
20/02/2014
(15 rows)

Can the same be done using any other clever trick ?

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

Here is my try :

staging::=> select  to_char(created_at,'DD/MM') || '/' || to_char(now(),'YYYY') as when from users;
    when    
------------
 24/02/2014
 28/02/2014
 02/03/2014
 01/03/2014
 04/03/2014
 02/03/2014
 06/03/2014
 07/05/2014
 02/06/2014
 06/06/2014
 20/02/2014
 20/02/2014
 20/02/2014
 20/06/2014
 20/02/2014
(15 rows)

Can the same be done using any other clever trick ? 

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;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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