On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes <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]http://en.wikipedia.org/wiki/Unix_time

> On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes

> <[2]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]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

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