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:
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
On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote: 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 rs
References
1. http://en.wikipedia.org/wiki/Unix_time 2. mailto:Niels.Nes@cwi.nl 3. http://p.sf.net/sfu/www-adobe-com 4. mailto:MonetDB-users@lists.sourceforge.net 5. https://lists.sourceforge.net/lists/listinfo/monetdb-users 6. http://www.cwi.nl/~niels 7. mailto:Niels.Nes@cwi.nl 8. http://p.sf.net/sfu/www-adobe-com 9. mailto:MonetDB-users@lists.sourceforge.net 10. 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