Extract quarter from date - users-list - monetdb.org

newer
Single Quotes VS Double Quotes

Extract quarter from date

older
Connecting Tableau to MonetDB

Diethard Steiner

15 Mar 2013 15 Mar '13

5:26 p.m.

Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders; I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract(quarter" SQLState: 42000 ErrorCode: 0 Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine. Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code. Thanks, Diethard

Attachments:

attachment.html (text/html — 682 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Niels Nes

16 Mar 16 Mar

9:54 a.m.

On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second. Niels -- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

Reply

Sign in to reply online Use email software

Diethard Steiner

4:36 p.m.

Hi Niels, Thanks a lot for clarifying this! Is there actually somewhere a detailed listing of all the supported functions? I couldn't find any info on this in the MonetDB SQL Documentation (unless of course I was not looking carefully enough). I was just trying various functions that I know from other DBs to extract the date/time part until I got one of them working, which is a bit tedious. It would be excellent if there was a documentation on date/time and string functions. Best regards, Diethard On Sat, Mar 16, 2013 at 9:54 AM, Niels Nes wrote:

On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

...
Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

...
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second.

Niels

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Reply

Sign in to reply online Use email software

Gennadiy

5:06 p.m.

You can obtain list of functions by executing the following query: select * from sys.functions; Gennadiy On Sat, 16 Mar 2013 16:36:23 +0000 Diethard Steiner wrote:

Hi Niels, Thanks a lot for clarifying this! Is there actually somewhere a detailed listing of all the supported functions? I couldn't find any info on this in the MonetDB SQL Documentation (unless of course I was not looking carefully enough). I was just trying various functions that I know from other DBs to extract the date/time part until I got one of them working, which is a bit tedious. It would be excellent if there was a documentation on date/time and string functions. Best regards, Diethard

On Sat, Mar 16, 2013 at 9:54 AM, Niels Nes wrote:

...
On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

...
Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

...
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second.

Niels

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Reply

Sign in to reply online Use email software

Diethard Steiner

17 Mar 17 Mar

10:56 a.m.

Many thanks for pointing this out! 1) I had a look at this table, but couldn't find any trunc function: SELECT * FROM sys.functions WHERE name LIKE '%extract%'; SELECT * FROM sys.functions WHERE func LIKE '%extract%'; 2) Moreover I am not quite sure how to read this table ... in example the name/function mod shows up several times with completely identical records. 3) In mclient, something like \d sys.* does not show me any of the tables. How can I see them? I can queried them though. I am logged on as monetdb user. [In Squirrel I can see them though in the object browser] 4) Is there a way to configure the default schema? For some reason tables imported like shown below end up in the sys schema (ideally I should have used below schema.table, but I'd still like to understand how to define a default schema, especially as I read somewhere that normally the default schema is tmp): *COPY 150000 RECORDS INTO customer FROM '/home/ec2-user/tpch_tables/customer.tbl';* Many thanks, Diethard On Sat, Mar 16, 2013 at 5:06 PM, Gennadiy wrote:

You can obtain list of functions by executing the following query:

select * from sys.functions;

Gennadiy

On Sat, 16 Mar 2013 16:36:23 +0000 Diethard Steiner wrote:

...
Hi Niels, Thanks a lot for clarifying this! Is there actually somewhere a detailed listing of all the supported functions? I couldn't find any info on this in the MonetDB SQL Documentation (unless of course I was not looking carefully enough). I was just trying various functions that I know from other DBs to extract the date/time part until I got one of them working, which is a bit tedious. It would be excellent if there was a documentation on date/time and string functions. Best regards, Diethard

On Sat, Mar 16, 2013 at 9:54 AM, Niels Nes wrote:

...
On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

...
Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

...
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second.

Niels

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Reply

Sign in to reply online Use email software

Brandon Jackson

12:51 p.m.

Use Delete From without a where clause to truncate a table or drop and recreate it. You should look at the getting started doc about loading the voc data on the MonetDB website. They show creating a user, a schema and then setting the default schema for the user. One bit of confusion I had when getting started is that when you connect with the URL and refer to a database, it's the name of the database created with the monetdbd command, not the schema. So you actually have to either create a user with a default schema or execute a SET SCHEMA command. This will straighten out your bulk loads too. Sent from my iPhone On Mar 17, 2013, at 5:56 AM, Diethard Steiner wrote:

Many thanks for pointing this out! 1) I had a look at this table, but couldn't find any trunc function: SELECT * FROM sys.functions WHERE name LIKE '%extract%'; SELECT * FROM sys.functions WHERE func LIKE '%extract%'; 2) Moreover I am not quite sure how to read this table ... in example the name/function mod shows up several times with completely identical records. 3) In mclient, something like \d sys.* does not show me any of the tables. How can I see them? I can queried them though. I am logged on as monetdb user. [In Squirrel I can see them though in the object browser] 4) Is there a way to configure the default schema? For some reason tables imported like shown below end up in the sys schema (ideally I should have used below schema.table, but I'd still like to understand how to define a default schema, especially as I read somewhere that normally the default schema is tmp): COPY 150000 RECORDS INTO customer FROM '/home/ec2-user/tpch_tables/customer.tbl'; Many thanks, Diethard

On Sat, Mar 16, 2013 at 5:06 PM, Gennadiy wrote:

...
You can obtain list of functions by executing the following query:

select * from sys.functions;

Gennadiy

On Sat, 16 Mar 2013 16:36:23 +0000 Diethard Steiner wrote:

...
Hi Niels, Thanks a lot for clarifying this! Is there actually somewhere a detailed listing of all the supported functions? I couldn't find any info on this in the MonetDB SQL Documentation (unless of course I was not looking carefully enough). I was just trying various functions that I know from other DBs to extract the date/time part until I got one of them working, which is a bit tedious. It would be excellent if there was a documentation on date/time and string functions. Best regards, Diethard

On Sat, Mar 16, 2013 at 9:54 AM, Niels Nes wrote:

...
On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

...
Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

...
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second.

Niels

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Reply

Sign in to reply online Use email software

Diethard Steiner

4:07 p.m.

Thanks a lot Brandon for clarifying this. So coming back to my earlier question: I'd like to get an overview of all the available SQL functions (i.e. EXTRACT), in particular date/time and string functions. I had a look at the various SQL related pages (i.e. http://www.monetdb.org/Documentation/Manuals/SQLreference/Features/Supported) but couldn't find this anywhere. Any pointers would be highly appreciated! Thanks a lot, Diethard On Sun, Mar 17, 2013 at 12:51 PM, Brandon Jackson wrote:

Use Delete From without a where clause to truncate a table or drop and recreate it.

You should look at the getting started doc about loading the voc data on the MonetDB website. They show creating a user, a schema and then setting the default schema for the user. One bit of confusion I had when getting started is that when you connect with the URL and refer to a database, it's the name of the database created with the monetdbd command, not the schema. So you actually have to either create a user with a default schema or execute a SET SCHEMA command. This will straighten out your bulk loads too.

Sent from my iPhone

On Mar 17, 2013, at 5:56 AM, Diethard Steiner wrote:

Many thanks for pointing this out! 1) I had a look at this table, but couldn't find any trunc function: SELECT * FROM sys.functions WHERE name LIKE '%extract%'; SELECT * FROM sys.functions WHERE func LIKE '%extract%'; 2) Moreover I am not quite sure how to read this table ... in example the name/function mod shows up several times with completely identical records. 3) In mclient, something like \d sys.* does not show me any of the tables. How can I see them? I can queried them though. I am logged on as monetdb user. [In Squirrel I can see them though in the object browser] 4) Is there a way to configure the default schema? For some reason tables imported like shown below end up in the sys schema (ideally I should have used below schema.table, but I'd still like to understand how to define a default schema, especially as I read somewhere that normally the default schema is tmp): *COPY 150000 RECORDS INTO customer FROM '/home/ec2-user/tpch_tables/customer.tbl';* Many thanks, Diethard

On Sat, Mar 16, 2013 at 5:06 PM, Gennadiy wrote:

...
You can obtain list of functions by executing the following query:

select * from sys.functions;

Gennadiy

On Sat, 16 Mar 2013 16:36:23 +0000 Diethard Steiner wrote:

...
Hi Niels, Thanks a lot for clarifying this! Is there actually somewhere a detailed listing of all the supported functions? I couldn't find any info on this in the MonetDB SQL Documentation (unless of course I was not looking carefully enough). I was just trying various functions that I know from other DBs to extract the date/time part until I got one of them working, which is a bit tedious. It would be excellent if there was a documentation on date/time and string functions. Best regards, Diethard

On Sat, Mar 16, 2013 at 9:54 AM, Niels Nes wrote:

...
On Fri, Mar 15, 2013 at 05:26:53PM +0000, Diethard Steiner wrote:

...
Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders;

I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract (quarter" SQLState: 42000 ErrorCode: 0

Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine.

Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code.

Thanks, Diethard

...
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

The EXTRACT functionality is limited to Year, Month, day, hour, minute, and second.

Niels

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Reply

Sign in to reply online Use email software

4304

Age (days ago)

4306

Last active (days ago)

Download

6 comments

4 participants

tags

participants (4)

Brandon Jackson
Diethard Steiner
Gennadiy
Niels Nes