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
> >
>
> > 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.
>
> > > _______________________________________________
> > > 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
> 6. https://lists.sourceforge.net/lists/listinfo/monetdb-user
> 7. http://www.cwi.nl/~niels
> 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