From: | James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Extract from date field |
Date: | 2011-06-18 17:02:14 |
Message-ID: | BANLkTinDtp6-4FPot0fS=XYHgFvzqYKihA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thanks Thom.
What does the ' ::int ' bit do at the end?
On Saturday, 18 June 2011, Thom Brown <thom(at)linux(dot)com> wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2011-06-18 17:23:40 | Re: Extract from date field |
Previous Message | LALIT KUMAR | 2011-06-18 13:35:34 | Re: Comparing dates in a pattern |