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_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