[MonetDB-users] SQL DATEADD equivalent
MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks
DATEADD isn't a standard SQL function, so could you tell us the expected semantics?
For string to date conversions you could load the file (src/sql/date.sql). Or run
create function str_to_date(s string, format string) returns date external name mtime."str_to_date";
This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could do a man strptime for details on the format string.
Niels
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi Niels, sure. Here it is:
dateadd(ss,1111111111,'1970-01-01')
This would add 1111111111 seconds to the epoch 1970-01-01 and give
2005-03-18 01:58:31.000
Reference: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858 and http://sqltutorials.blogspot.com/2007/06/sql-dateadd-function.html
Thanks.
On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks
DATEADD isn't a standard SQL function, so could you tell us the expected semantics?
For string to date conversions you could load the file (src/sql/date.sql). Or run
create function str_to_date(s string, format string) returns date external name mtime."str_to_date";
This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could do a man strptime for details on the format string.
Niels
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned integer. For example: 1230648143. This is the number of second from epoch: 1970-01-01 00:00:00 reference: http://en.wikipedia.org/wiki/Unix_time
On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks
DATEADD isn't a standard SQL function, so could you tell us the expected semantics?
For string to date conversions you could load the file (src/sql/date.sql). Or run
create function str_to_date(s string, format string) returns date external name mtime."str_to_date";
This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could do a man strptime for details on the format string.
Niels
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned integer. For example: 1230648143. This is the number of second from epoch: 1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple
Niels
reference: [1]http://en.wikipedia.org/wiki/Unix_time On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes <[2]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I need
it to
convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tell us the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string) returns date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could do a man strptime for details on the format string. Niels > ------------------------------------------------------------ ------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. [3]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [4]MonetDB-users@lists.sourceforge.net > [5]https://lists.sourceforge.net/lists/listinfo/monetdb-user s -- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [6]http://www.cwi.nl/~niels e-mail: [7]Niels.Nes@cwi.nl ------------------------------------------------------------ ------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [8]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [9]MonetDB-users@lists.sourceforge.net [10]https://lists.sourceforge.net/lists/listinfo/monetdb-use rsReferences
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Great thanks! So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned integer. For example: 1230648143. This is the number of second from epoch: 1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple
Niels
reference: [1]http://en.wikipedia.org/wiki/Unix_time On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes <[2]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I need
it to
convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tell us the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string) returns date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could do a man strptime for details on the format string. Niels > ------------------------------------------------------------ ------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. [3]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [4]MonetDB-users@lists.sourceforge.net > [5]https://lists.sourceforge.net/lists/listinfo/monetdb-user s -- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [6]http://www.cwi.nl/~niels e-mail: [7]Niels.Nes@cwi.nl ------------------------------------------------------------ ------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [8]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [9]MonetDB-users@lists.sourceforge.net [10]https://lists.sourceforge.net/lists/listinfo/monetdb-use rsReferences
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned
integer. For
example: 1230648143. This is the number of second from
epoch:
1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_timeOn Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
<[2][3]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I
need
it to
convert Unix time on-the-fly (700milliion times for
each
query!) . Could anyone recommend a fast way to do
this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tellus
the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string)returns
date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could doa
man strptime for details on the format string. Niels >
------------------ > Apps built with the Adobe(R) Flex(R) framework andFlex
Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding andstep-through
debugging. > Download the free 60 day trial.> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u sers -- Niels Nes, Centre for Mathematics and Computer Science(CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platformcapabilities.
Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding andstep-through
debugging. Download the free 60 day trial.> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
Apps built with the Adobe(R) Flex(R) framework and Flex
Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities.
Quickly and
easily build your RIAs with Flex Builder, the
Eclipse(TM)based development
software that enables intelligent coding and step-through
debugging.
Download the free 60 day trial.
[22]http://p.sf.net/sfu/www-adobe-com
MonetDB-users mailing list [23]MonetDB-users@lists.sourceforge.net
[24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
- mailto:Niels.Nes@cwi.nl
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-user
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-use
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Great. It works fine. Took a bit longer than just adding a constant 1111111111 seconds though. (about 11 fold increase in time). Should I bother with indexing? Or is MonetDB going to take care of all that?
Thx
On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned
integer. For
example: 1230648143. This is the number of second from
epoch:
1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_timeOn Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
<[2][3]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I
need
it to
convert Unix time on-the-fly (700milliion times for
each
query!) . Could anyone recommend a fast way to do
this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tellus
the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string)returns
date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could doa
man strptime for details on the format string. Niels >
------------------ > Apps built with the Adobe(R) Flex(R) framework andFlex
Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding andstep-through
debugging. > Download the free 60 day trial.> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u sers -- Niels Nes, Centre for Mathematics and Computer Science(CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platformcapabilities.
Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding andstep-through
debugging. Download the free 60 day trial.> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
Apps built with the Adobe(R) Flex(R) framework and Flex
Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities.
Quickly and
easily build your RIAs with Flex Builder, the
Eclipse(TM)based development
software that enables intelligent coding and step-through
debugging.
Download the free 60 day trial.
[22]http://p.sf.net/sfu/www-adobe-com
MonetDB-users mailing list [23]MonetDB-users@lists.sourceforge.net
[24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
- mailto:Niels.Nes@cwi.nl
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-user
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-use
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Fri, Mar 20, 2009 at 10:52 PM, Yue Sheng yuesheng8@gmail.com wrote:
Great. It works fine. Took a bit longer than just adding a constant 1111111111 seconds though. (about 11 fold increase in time). Should I bother with indexing? Or is MonetDB going to take care of all that? Thx
You should define a sorting on the columns that are more often used for select/join etc. I am not aware of the exact SQL commands but the SQL wizards on the list can help on this.
On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote: > There's no string involved. Just to make doubly clear, my > "dates" are in Unix time format, so 32bit unsigned integer. For > example: 1230648143. This is the number of second from epoch: > 1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_time
> On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
> <[2][3]Niels.Nes@cwi.nl> wrote: > > On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote: > > MonetDB doesn't seem to support DATEADD function. I need > it to > > convert Unix time on-the-fly (700milliion times for each > > query!) . Could anyone recommend a fast way to do this > please? > > Thanks > > DATEADD isn't a standard SQL function, so could you tell us > the > expected semantics? > For string to date conversions you could load the file > (src/sql/date.sql). > Or run > create function str_to_date(s string, format string) returns > date > external name mtime."str_to_date"; > This adds the function str_to_date, which needs a string > column and > a format string. It depends on strptime, so you could do a > man > strptime for details on the format string. > Niels > > > ------------------------------------------------------------ > ------------------ > > Apps built with the Adobe(R) Flex(R) framework and Flex > Builder(TM) are > > powering Web 2.0 with engaging, cross-platform > capabilities. Quickly and > > easily build your RIAs with Flex Builder, the > Eclipse(TM)based development > > software that enables intelligent coding and step-through > debugging. > > Download the free 60 day trial.
> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u ser
> s > -- > Niels Nes, Centre for Mathematics and Computer Science (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
> ------------------------------------------------------------ > ------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex > Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. > Quickly and > easily build your RIAs with Flex Builder, the > Eclipse(TM)based development > software that enables intelligent coding and step-through > debugging. > Download the free 60 day trial.
> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl
> 3. [14]http://p.sf.net/sfu/www-adobe-com
> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl
> 8. [19]http://p.sf.net/sfu/www-adobe-com
> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
> --------------------------------------------------------------- --------------- > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. [22]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [23]MonetDB-users@lists.sourceforge.net > [24]https://lists.sourceforge.net/lists/listinfo/monetdb-users -- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl --------------------------------------------------------------- --------------- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
1. mailto:Niels.Nes@cwi.nl 2. http://en.wikipedia.org/wiki/Unix_time 3. mailto:Niels.Nes@cwi.nl 4. http://p.sf.net/sfu/www-adobe-com 5. mailto:MonetDB-users@lists.sourceforge.net 6. https://lists.sourceforge.net/lists/listinfo/monetdb-user 7. http://www.cwi.nl/~niels 8. mailto:Niels.Nes@cwi.nl 9. http://p.sf.net/sfu/www-adobe-com 10. mailto:MonetDB-users@lists.sourceforge.net 11. https://lists.sourceforge.net/lists/listinfo/monetdb-use 12. http://en.wikipedia.org/wiki/Unix_time 13. mailto:Niels.Nes@cwi.nl 14. http://p.sf.net/sfu/www-adobe-com 15. mailto:MonetDB-users@lists.sourceforge.net 16. https://lists.sourceforge.net/lists/listinfo/monetdb-users 17. http://www.cwi.nl/~niels 18. mailto:Niels.Nes@cwi.nl 19. http://p.sf.net/sfu/www-adobe-com 20. mailto:MonetDB-users@lists.sourceforge.net 21. https://lists.sourceforge.net/lists/listinfo/monetdb-users 22. http://p.sf.net/sfu/www-adobe-com 23. mailto:MonetDB-users@lists.sourceforge.net 24. https://lists.sourceforge.net/lists/listinfo/monetdb-users 25. http://www.cwi.nl/~niels 26. mailto:Niels.Nes@cwi.nl 27. http://p.sf.net/sfu/www-adobe-com 28. mailto:MonetDB-users@lists.sourceforge.net 29. https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Lefteris wrote:
On Fri, Mar 20, 2009 at 10:52 PM, Yue Sheng yuesheng8@gmail.com wrote:
Great. It works fine. Took a bit longer than just adding a constant 1111111111 seconds though. (about 11 fold increase in time). Should I bother with indexing? Or is MonetDB going to take care of all that? Thx
You should define a sorting on the columns that are more often used for select/join etc. I am not aware of the exact SQL commands but the SQL wizards on the list can help on this.
Sortedness is detected upon insert of individual tuples, but as always there can be just one physical ordering for the table at large. Everything beyond that point is for technical DBAs and programmers.
The indexing aspect of MonetDB has been documented in the various science papers. To read the latest on that topic, we refer to the cracking papers. See our science library.
On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned
integer. For
example: 1230648143. This is the number of second from
epoch:
1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_timeOn Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
<[2][3]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I
need
it to
convert Unix time on-the-fly (700milliion times for
each
query!) . Could anyone recommend a fast way to do
this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tellus
the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string)returns
date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could doa
man strptime for details on the format string. Niels >
------------------ > Apps built with the Adobe(R) Flex(R) framework andFlex
Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding andstep-through
debugging. > Download the free 60 day trial.> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u sers -- Niels Nes, Centre for Mathematics and Computer Science(CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platformcapabilities.
Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding andstep-through
debugging. Download the free 60 day trial.> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
Apps built with the Adobe(R) Flex(R) framework and Flex
Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities.
Quickly and
easily build your RIAs with Flex Builder, the
Eclipse(TM)based development
software that enables intelligent coding and step-through
debugging.
Download the free 60 day trial.
[22]http://p.sf.net/sfu/www-adobe-com
MonetDB-users mailing list [23]MonetDB-users@lists.sourceforge.net
[24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
- mailto:Niels.Nes@cwi.nl
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-user
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-use
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Sure, I'll take a look at the paper for more in depth stuff. Just on the quick:
Say I want to do:
"select * from bigtable where col1 = 'peter'l;"
Will an SQL index create:
"create index col1_index on bigtable (col1);"
be simply ignored by MonetDB?
On Fri, Mar 20, 2009 at 3:08 PM, Martin Kersten Martin.Kersten@cwi.nlwrote:
Lefteris wrote:
On Fri, Mar 20, 2009 at 10:52 PM, Yue Sheng yuesheng8@gmail.com wrote:
Great. It works fine. Took a bit longer than just adding a constant 1111111111 seconds though. (about 11 fold increase in time). Should I bother with indexing? Or is MonetDB going to take care of all
that?
Thx
You should define a sorting on the columns that are more often used for select/join etc. I am not aware of the exact SQL commands but the SQL wizards on the list can help on this.
Sortedness is detected upon insert of individual tuples, but as always there can be just one physical ordering for the table at large. Everything beyond that point is for technical DBAs and programmers.
The indexing aspect of MonetDB has been documented in the various science papers. To read the latest on that topic, we refer to the cracking papers. See our science library.
On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned
integer. For
example: 1230648143. This is the number of second from
epoch:
1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_timeOn Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
<[2][3]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote: > MonetDB doesn't seem to support DATEADD function. I
need
it to > convert Unix time on-the-fly (700milliion times for
each
> query!) . Could anyone recommend a fast way to do
this
please? > Thanks
DATEADD isn't a standard SQL function, so could you tellus
the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string)returns
date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could doa
man strptime for details on the format string. Niels >
------------------ > Apps built with the Adobe(R) Flex(R) framework andFlex
Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding andstep-through
debugging. > Download the free 60 day trial.> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u sers -- Niels Nes, Centre for Mathematics and Computer Science(CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platformcapabilities.
Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding andstep-through
debugging. Download the free 60 day trial.> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
Apps built with the Adobe(R) Flex(R) framework and Flex
Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities.
Quickly and
easily build your RIAs with Flex Builder, the
Eclipse(TM)based development
software that enables intelligent coding and step-through
debugging.
Download the free 60 day trial.
[22]http://p.sf.net/sfu/www-adobe-com
MonetDB-users mailing list [23]MonetDB-users@lists.sourceforge.net
[24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
- mailto:Niels.Nes@cwi.nl
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-user
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-use
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM)
are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly
and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly
and
easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
It is currently sorted (by construction) by a column of unix time stamp, the very column that I use in the query.
On Fri, Mar 20, 2009 at 2:59 PM, Lefteris lsidir@gmail.com wrote:
On Fri, Mar 20, 2009 at 10:52 PM, Yue Sheng yuesheng8@gmail.com wrote:
Great. It works fine. Took a bit longer than just adding a constant 1111111111 seconds though. (about 11 fold increase in time). Should I bother with indexing? Or is MonetDB going to take care of all
that?
Thx
You should define a sorting on the columns that are more often used for select/join etc. I am not aware of the exact SQL commands but the SQL wizards on the list can help on this.
On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
Great thanks!
So i just need to do
sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4 second;
probably a cast is the proper statement.
Cast (col4 as interval second)
Niels
to add, for each row, the number of seconds in col4 to the epoch.
On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <[1]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
There's no string involved. Just to make doubly clear, my "dates" are in Unix time format, so 32bit unsigned
integer. For
example: 1230648143. This is the number of second from
epoch:
1970-01-01 00:00:00
sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1' second; +----------------------------+ | sql_add_single_value | +============================+ | 1970-01-01 00:00:01.000000 | +----------------------------+ 1 tuple sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1111111111' second; +----------------------------+ | sql_add_single_value | +============================+ | 2005-03-18 01:58:31.000000 | +----------------------------+ 1 tuple Niels > > reference: [1][2]http://en.wikipedia.org/wiki/Unix_timeOn Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
<[2][3]Niels.Nes@cwi.nl> wrote:
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
MonetDB doesn't seem to support DATEADD function. I
need
it to
convert Unix time on-the-fly (700milliion times for
each
query!) . Could anyone recommend a fast way to do
this
please?
Thanks
DATEADD isn't a standard SQL function, so could you tellus
the expected semantics? For string to date conversions you could load the file (src/sql/date.sql). Or run create function str_to_date(s string, format string)returns
date external name mtime."str_to_date"; This adds the function str_to_date, which needs a string column and a format string. It depends on strptime, so you could doa
man strptime for details on the format string. Niels >
------------------ > Apps built with the Adobe(R) Flex(R) framework andFlex
Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding andstep-through
debugging. > Download the free 60 day trial.> [3][4]http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > [4][5]MonetDB-users@lists.sourceforge.net > > > [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u sers -- Niels Nes, Centre for Mathematics and Computer Science(CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20592-4312
> url: [6][7]http://www.cwi.nl/~niels e-mail: > [7][8]Niels.Nes@cwi.nl
------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platformcapabilities.
Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding andstep-through
debugging. Download the free 60 day trial.> [8][9]http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > [9][10]MonetDB-users@lists.sourceforge.net > [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb -use > rs > > References > > 1. [12]http://en.wikipedia.org/wiki/Unix_time > 2. mailto:[13]Niels.Nes@cwi.nl> 4. mailto:[15]MonetDB-users@lists.sourceforge.net > 5. [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs > 6. [17]http://www.cwi.nl/~niels > 7. mailto:[18]Niels.Nes@cwi.nl> 9. mailto:[20]MonetDB-users@lists.sourceforge.net > 10. [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use rs
Apps built with the Adobe(R) Flex(R) framework and Flex
Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities.
Quickly and
easily build your RIAs with Flex Builder, the
Eclipse(TM)based development
software that enables intelligent coding and step-through
debugging.
Download the free 60 day trial.
[22]http://p.sf.net/sfu/www-adobe-com
MonetDB-users mailing list [23]MonetDB-users@lists.sourceforge.net
[24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: [25]http://www.cwi.nl/~niels e-mail: [26]Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. [27]http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list [28]MonetDB-users@lists.sourceforge.net [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
References
- mailto:Niels.Nes@cwi.nl
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-user
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-use
- http://en.wikipedia.org/wiki/Unix_time
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
- http://www.cwi.nl/~niels
- mailto:Niels.Nes@cwi.nl
- http://p.sf.net/sfu/www-adobe-com
- mailto:MonetDB-users@lists.sourceforge.net
- https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM)
are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly
and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based
development
software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (4)
-
Lefteris -
Martin Kersten -
Niels Nes -
Yue Sheng