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

Extract from date field

From: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Extract from date field
Date: 2011-06-17 11:11:52
Message-ID: BANLkTinbJhDbaQe11zmzm2wuQo--tsn+DQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Oliveiros d'Azevedo CristinaDate: 2011-06-17 11:32:19
Subject: Re: compare date in two rows
Previous:From: Oliveiros d'Azevedo CristinaDate: 2011-06-17 11:10:33
Subject: Re: compare date in two rows

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