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 10:52 PM, Yue Sheng <yuesheng8@gmail.com> wrote:You should define a sorting on the columns that are more often used
> 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
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
>
>
------------------------------------------------------------------------------
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