Обсуждение: Extract from date field
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
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 >
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
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 >
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
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
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