Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extract from date field

Extract from date field

From
James David Smith
Date:
Dear all,

I want to make a primary key for my table based upon a few other
columns. I do this with the following code:

ALTER TABLE camdengps2
ADD COLUMN camdencrimes_link varchar;
UPDATE camdengps2
SET camdencrimes_link='' || EXTRACT(YEAR FROM date_time) || '0' ||
EXTRACT(MONTH FROM date_time) || '' || EXTRACT(DAY FROM date_time) ||
'' || incident

Essentially I am truncating the year, month, day and another field
called 'Incident'. However I am finding two problems with this:

1) I would like the result to be an integer rather than a varchar.
However when I set the field to be integer rather than varchar the SET
query doesn't work. I guess I maybe need to CAST the date as an
integer somehow?

2) When I extract the DAY from the date field, instead of the 2nd of
the month becoming '02' it actually becomes '2'. I would like it to be
'02'.

Any help greatly appreciated...

Best wishes

James

Re: Extract from date field

From
James David Smith
Date:
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...

James


On 17 June 2011 12:11, James David Smith <james.david.smith@gmail.com> wrote:
> Dear all,
>
> I want to make a primary key for my table based upon a few other
> columns. I do this with the following code:
>
> ALTER TABLE camdengps2
> ADD COLUMN camdencrimes_link varchar;
> UPDATE camdengps2
> SET camdencrimes_link='' || EXTRACT(YEAR FROM date_time) || '0' ||
> EXTRACT(MONTH FROM date_time) || '' || EXTRACT(DAY FROM date_time) ||
> '' || incident
>
> Essentially I am truncating the year, month, day and another field
> called 'Incident'. However I am finding two problems with this:
>
> 1) I would like the result to be an integer rather than a varchar.
> However when I set the field to be integer rather than varchar the SET
> query doesn't work. I guess I maybe need to CAST the date as an
> integer somehow?
>
> 2) When I extract the DAY from the date field, instead of the 2nd of
> the month becoming '02' it actually becomes '2'. I would like it to be
> '02'.
>
> Any help greatly appreciated...
>
> Best wishes
>
> James
>

Re: Extract from date field

From
Thom Brown
Date:
On 17 June 2011 20:12, James David Smith <james.david.smith@gmail.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

Re: Extract from date field

From
James David Smith
Date:
Thanks Thom.

What does the ' ::int  ' bit do at the end?






On Saturday, 18 June 2011, Thom Brown <thom@linux.com> wrote:
> On 17 June 2011 20:12, James David Smith <james.david.smith@gmail.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
>

Re: Extract from date field

From
Thom Brown
Date:
On 18 June 2011 18:02, James David Smith <james.david.smith@gmail.com> wrote:
> Thanks Thom.
>
> What does the ' ::int  ' bit do at the end?

That casts the whole lot to an int value so that it can be assigned to
the column you're updating.  Otherwise you could end up with an error
message saying the types don't match.

So ::int is equivalent to cast(column as int).

Thom

Re: Extract from date field

From
Thom Brown
Date:
On 18 June 2011 00:05, Thom Brown <thom@linux.com> wrote:
> On 17 June 2011 20:12, James David Smith <james.david.smith@gmail.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.

Just to touch on your original problem though.  You said you were
trying to create a primary key out of those 2 columns.  You can,
however, define a primary key using multiple columns like so:

ALTER TABLE my_table ADD PRIMARY KEY (column_1, column_2);

This would then enforce a unique constraint based on a combination of
those columns.

So if the above statement were run on a table with 3 columns, you could insert:

1,1,2
1,2,1
2,2,1
2,1,1

As the first 2 column value are never duplicated (and the 3rd isn't
checked).  But you couldn't do:

1,1,2
1,1,1

As this would mean column_1 and column_2 combined would be repeated
thus violating the primary key to identify a row.

The reason this is relevant is because if you've created a new column
as a primary key based on a combination of data from 2 other columns,
you will always have to know the exact value for the new column rather
than relying on PostgreSQL calculating based on other columns.  The
update works on the existing table because the primary key isn't
enforced so the value didn't already have to be there.  But once you
add the primary key, you'll have to insert this value yourself when
you add a new row.

So essentially you'd have to do something like:

INSERT INTO camdengps2 (date_time, incident, camdencrimes_link)
VALUES ('2011-04-18', 4, (to_char('2011-04-18'::timestamp, 'DDMMYYYY')
|| incident))::int);

Or, depending on what you're trying to do, you may just wish to have
this value created on-the-fly in a view:

CREATE VIEW camdengps_full AS
SELECT (to_char(date_time, 'DDMMYYYY') || incident)::int AS
camdencrimes_link, date_time, incident
FROM camdengps2;

Then there would be no extra column to update as it's calculated every
time you query the view, although if you needed to return hundreds of
thousands of rows each time, this could be computationally expensive.

But since I don't actually know the ultimate purpose of your question,
this may or may not be relevant.  I just thought I'd add it in case it
allows you to solve it differently.

Thom

Re: Extract from date field

From
Thom Brown
Date:
On 18 June 2011 18:54, Thom Brown <thom@linux.com> wrote:
> So essentially you'd have to do something like:
>
> INSERT INTO camdengps2 (date_time, incident, camdencrimes_link)
> VALUES ('2011-04-18', 4, (to_char('2011-04-18'::timestamp, 'DDMMYYYY')
> || incident))::int);

Correction, that last bit shouldn't be incident but just 4 on its own
in order to demonstrate my point.

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

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