Skip site navigation (1) Skip section navigation (2)

Re: Extract from date field

From: Thom Brown <thom(at)linux(dot)com>
To: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Extract from date field
Date: 2011-06-17 23:05:13
Message-ID: BANLkTinkkOpV76K3bwyRYSczAqD0WkGLng@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 17 June 2011 20:12, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> Hey,
>
> I realise it's probably poor form to reply to your own question, but I
> thought I'd just say I've managed to do this! Well, question 2 anyway.
> To extract a DAY from a date field, and keep a leading zero, I have
> used the 'lpad' function like so:
>
> SELECT lpad (cast((EXTRACT(DAY from timestamp '2010-01-01')) as
> varchar), 2, '0')
>
> Except that in my query it looks like this:
>
> (SELECT lpad (cast((EXTRACT(DAY from date_of_incident)) as varchar), 2, '0'))
>
> I thought I'd post this incase it's off use to anyone else.
>
> Just need to figure out how to convert it all to a integer now...

Ah, I believe I've replied to this on Twitter, but I'll post here too.
 You can use the very handy to_char function to convert your date into
a formatted string. (see
http://www.postgresql.org/docs/9.0/static/functions-formatting.html )
So you'd end up with:

ALTER TABLE camdengps2
ADD COLUMN camdencrimes_link varchar;
UPDATE camdengps2
SET camdencrimes_link=(to_char(date_time, 'DDMMYYYY') || incident)::int

Hope that solves it for you.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgsql-novice by date

Next:From: LALIT KUMARDate: 2011-06-18 11:38:05
Subject: Comparing dates in a pattern
Previous:From: James David SmithDate: 2011-06-17 19:12:49
Subject: Re: Extract from date field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group