Quantcast

equivalent of date_trunc

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

equivalent of date_trunc

Ahmed El-Daly
Hello,

We have built a reporting DB for our company using postgres which is quickly becoming too slow to use. We are looking at luciddb, but have a question regarding our current ETL statements. We currently roll up our data by day into the reporting DB through statements such as:

SELECT date_trunc('day',t.transaction_date) as date, sites.uid, SUM(t.amount), count(t.amount), users.user_commission FROM transactions t
          JOIN tracking_codes ON t.tracking_code = tracking_codes.tracking_code AND tracking_codes.product_type_id = '123'
          JOIN sites ON sites.uid = tracking_codes.site_id AND sites.uid IN (SELECT s.uid FROM sites s JOIN users ON s.user_id=users.id AND users.status='activated')
          JOIN users ON sites.user_id = users.id
          WHERE t.transaction_date >= '#{@start_date.beginning_of_day.to_s(:db)}' AND
          t.transaction_date < '#{@end_date.end_of_day.to_s(:db)}'
          GROUP BY users.user_commission, sites.uid, date, sites.user_id

I was wondering how we would go about doing something similar, seeing that I don't see an equivalent of date_trunc. I've tried using (EXTRACT( field FROM interval )), but I don't think it's what I want seeing that it needs an interval and those are date/time fields.

Thanks for the help,
Ahmed

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

equivalent of date_trunc

Ahmed El-Daly
Hello,

We have built a reporting DB for our company using postgres which is quickly becoming too slow to use. We are looking at luciddb, but have a question regarding our current ETL statements. We currently roll up our data by day into the reporting DB through statements such as:

SELECT date_trunc('day',t.transaction_date) as date, sites.uid, SUM(t.amount), count(t.amount), users.user_commission FROM transactions t
          JOIN tracking_codes ON t.tracking_code = tracking_codes.tracking_code AND tracking_codes.product_type_id = '123'
          JOIN sites ON sites.uid = tracking_codes.site_id AND sites.uid IN (SELECT s.uid FROM sites s JOIN users ON s.user_id=users.id AND users.status='activated')
          JOIN users ON sites.user_id = users.id
          WHERE t.transaction_date >= '#{@start_date.beginning_of_day.to_s(:db)}' AND
          t.transaction_date < '#{@end_date.end_of_day.to_s(:db)}'
          GROUP BY users.user_commission, sites.uid, date, sites.user_id

I was wondering how we would go about doing something similar, seeing that I don't see an equivalent of date_trunc. I've tried using (EXTRACT( field FROM interval )), but I don't think it's what I want seeing that it needs an interval and those are date/time fields.

Thanks for the help,
Ahmed

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

Julian Hyde
In reply to this post by Ahmed El-Daly
As you may know, SQLstream is an extension to Eigenbase technology (effectively an extension to LucidDB) with streaming SQL, and that includes more support for time-oriented data. Those extensions include a function FLOOR(<datetime> TO <timeunit>). For example, our FLOOR(t.transaction_date TO DAY) would achieve the same effect as Postgres date_trunc('day',t.transaction_date).
 
<datetime> is any date, time or timestamp value, and <timeunit> is a standard SQL timeunit, {SECOND, HOUR, MINUTE, DAY, MONTH, YEAR}. We also have CEIL(<datetime> TO <timeunit>).
 
If you think those functions would be useful, I can ask SQLstream's permission to contribute them.
 
Julian


From: Ahmed El-Daly [mailto:[hidden email]]
Sent: Tuesday, December 22, 2009 2:00 PM
To: [hidden email]
Subject: [luciddb-users] equivalent of date_trunc

Hello,

We have built a reporting DB for our company using postgres which is quickly becoming too slow to use. We are looking at luciddb, but have a question regarding our current ETL statements. We currently roll up our data by day into the reporting DB through statements such as:

SELECT date_trunc('day',t.transaction_date) as date, sites.uid, SUM(t.amount), count(t.amount), users.user_commission FROM transactions t
          JOIN tracking_codes ON t.tracking_code = tracking_codes.tracking_code AND tracking_codes.product_type_id = '123'
          JOIN sites ON sites.uid = tracking_codes.site_id AND sites.uid IN (SELECT s.uid FROM sites s JOIN users ON s.user_id=users.id AND users.status='activated')
          JOIN users ON sites.user_id = users.id
          WHERE t.transaction_date >= '#{@start_date.beginning_of_day.to_s(:db)}' AND
          t.transaction_date < '#{@end_date.end_of_day.to_s(:db)}'
          GROUP BY users.user_commission, sites.uid, date, sites.user_id

I was wondering how we would go about doing something similar, seeing that I don't see an equivalent of date_trunc. I've tried using (EXTRACT( field FROM interval )), but I don't think it's what I want seeing that it needs an interval and those are date/time fields.

Thanks for the help,
Ahmed

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

ngoodman
In reply to this post by Ahmed El-Daly

On Dec 22, 2009, at 2:02 PM, Ahmed El-Daly wrote:

I was wondering how we would go about doing something similar, seeing that I don't see an equivalent of date_trunc. I've tried using (EXTRACT( field FROM interval )), but I don't think it's what I want seeing that it needs an interval and those are date/time fields.

I think the act of casting a TIMESTAMP to a DATE does what you want:

0: jdbc:luciddb:> select cast (ts as DATE) from (select cast('2008-12-22 22:11:00' as TIMESTAMP) as ts from (values(0)));
+-------------+
|   EXPR$0    |
+-------------+
| 2008-12-22  |
+-------------+

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

ngoodman
In reply to this post by Julian Hyde

On Dec 22, 2009, at 2:53 PM, Julian Hyde wrote:

As you may know, SQLstream is an extension to Eigenbase technology (effectively an extension to LucidDB) with streaming SQL, and that includes more support for time-oriented data. Those extensions include a function FLOOR(<datetime> TO <timeunit>). For example, our FLOOR(t.transaction_date TO DAY) would achieve the same effect as Postgres date_trunc('day',t.transaction_date).

And, this would be better!!!  :)

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

John Sichi
Administrator
If you only need truncate-to-date, you can just cast(ts as DATE).  For
finer granularity, a solution until the SQLstream extensions are
available in the language would be to write your own date_trunc UDF.

http://pub.eigenbase.org/wiki/LucidDbUdfJavaHowto

JVS

Nicholas Goodman wrote:
>
> On Dec 22, 2009, at 2:53 PM, Julian Hyde wrote:
>
>> As you may know, SQLstream is an extension to Eigenbase technology
>> (effectively an extension to LucidDB) with streaming SQL, and that
>> includes more support for time-oriented data. Those extensions include
>> a function FLOOR(<datetime> TO <timeunit>). For example, our
>> FLOOR(t.transaction_date TO DAY) would achieve the same effect as
>> Postgres date_trunc('day',t.transaction_date).

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

Francisco Reyes-3
John V. Sichi writes:

> If you only need truncate-to-date, you can just cast(ts as DATE).  For

Lastly, although not a Lucid "solution" you could use views on Postgres.
Personally I like views better so then I can map the Lucid data defintion to
a view.

The other consideration may be which machine has more cycles to spare
during the ETL window. If the postgres machine is more heavily used and the
Lucid machine is less loaded, at least during the ETL period, then it may be
better to let Lucid handle the conversion through one of the previously
suggested methods in this thread. If the opposite is true, and the Lucid
machine is more heavily used, then it makes sense to do as much as possible
on the Postgres machine.


------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: equivalent of date_trunc

Ahmed El-Daly
In reply to this post by Ahmed El-Daly
Thanks all for your answers.

This works perfectly:
SELECT cast(t."transaction_date" as date), SUM(t."amount"), count(t."amount") FROM remote_server."transactions" t
GROUP BY t."transaction_date";
------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...