Thread: Help On Postgresql
Dear Friends
I am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd.
I have some questions regarding Postgresql which are listed below:
a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a Date/DateTime variable.
For Example : i> In Oracle to_char(dt,'yyyy') - for extracting the year value from a date variable dt
ii> In SqlServer datepart(hh,dt) - for extracting the Hour value from a date variable dt
b) Can I create an identity column in Postgresql the value of which column will start from 1 say and keep
on incrementing by 1 OR how can I serve my purpose in any other way.
Please do let me know if any one of you have any solution.
Regards
Jaydip
Jaydip wrote: > Dear Friends > > I am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd. > > I have some questions regarding Postgresql which are listed below: > > a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a Da= > te/DateTime variable. > > For Example : i> In Oracle to_char(dt,'yyyy') - for extracting the year= > value from a date variable dt > ii> In SqlServer datepart(hh,dt) - for extracting = > the Hour value from a date variable dt I don't know what variables you are talking about. Which interface do yo use ? My client programs recieve plain text from postgres, so there are no variables. > b) Can I create an identity column in Postgresql the value of which column = > will start from 1 say and keep > on incrementing by 1 OR how can I serve my purpose in any other way. Use the data type SERIAL. If you say e.g. CREATE TABLE mytable ( key SERIAL, .... ); Then every time you insert a new row key will increment automatically. Arne.
a) Look at "Date/Time Functions" in the docs: specifically, listed under "Functions and Operators" in the Users Guide.
b) "Serial" type will do this. Look under "Numeric Types" , in the "Data Types" section of the Users Guide.
----- Original Message -----From: JaydipSent: Thursday, August 23, 2001 2:50 AMSubject: Help On PostgresqlDear FriendsI am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd.I have some questions regarding Postgresql which are listed below:a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a Date/DateTime variable.For Example : i> In Oracle to_char(dt,'yyyy') - for extracting the year value from a date variable dtii> In SqlServer datepart(hh,dt) - for extracting the Hour value from a date variable dtb) Can I create an identity column in Postgresql the value of which column will start from 1 say and keepon incrementing by 1 OR how can I serve my purpose in any other way.Please do let me know if any one of you have any solution.RegardsJaydip
On Thu, 23 Aug 2001, Jaydip wrote: > a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a Date/DateTime variable. > > For Example : i> In Oracle to_char(dt,'yyyy') - for extracting the year value from a date variable dt > ii> In SqlServer datepart(hh,dt) - for extracting the Hour value from a date variable dt I'm not sure what versions all of these came in at, but:There is a to_char which should be somewhat compatible with oracle's,aswell as a datepart (whose part names are like 'year', 'month', ...)and extract(<part> from dt) > b) Can I create an identity column in Postgresql the value of which column will start from 1 say and keep > on incrementing by 1 OR how can I serve my purpose in any other way. Look at sequences and the serial pseudo-type.
a) Either of these should return the yaer (2001). See section 4.7 of the user guide SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); SELECT date_part('year', TIMESTAMP '2001-02-16 20:38:40'); b) The serial datatype will do this. It is actually just an int4 with a default value of nextval('<sequence name>'). - Stuart > -----Original Message----- > From: Jaydip [SMTP:jaydip@ontrackindia.com] > Sent: Thursday, August 23, 2001 8:51 AM > To: pgsql-sql@postgresql.org > Subject: Help On Postgresql > > Dear Friends > > I am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd. > > I have some questions regarding Postgresql which are listed below: > > a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a > Date/DateTime variable. > > For Example : i> In Oracle to_char(dt,'yyyy') - for extracting the > year value from a date variable dt > ii> In SqlServer datepart(hh,dt) - for extracting > the Hour value from a date variable dt > > b) Can I create an identity column in Postgresql the value of which column > will start from 1 say and keep > on incrementing by 1 OR how can I serve my purpose in any other way. > > > Please do let me know if any one of you have any solution. > > Regards > > Jaydip