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
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:
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
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote: 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
> 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
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