samedi 15 octobre 2016

Laravel postgres current_timestamp

Hi I have a problem in current timestamp query I am using laravel and postgres. my query is like this

 $min_max_avg = DB::table('token_queue')                             
    ->join('token', 'token.id', '=', 'token_queue.token_id')                             
    ->select(DB::raw('MIN(serving_end_time - serving_start_time) AS min_total_serving_time'))                                                       
    ->whereBetween('token.generated_time',[DB::raw("date_trunc('hour', CURRENT_TIMESTAMP)"), 
                                           DB::raw("date_trunc('hour', CURRENT_TIMESTAMP + interval '1 hour')")])
    ->first();

which generate sql like this

select 
MIN(serving_end_time - serving_start_time) AS min_total_serving_time
from "token_queue" inner join "token" on "token"."id" = "token_queue"."token_id" 
where "token"."generated_time" between date_trunc('hour', CURRENT_TIMESTAMP) and date_trunc('hour', CURRENT_TIMESTAMP + interval '1 hour') 

so when i run this sql in pgAdmin it work fine but it laravel in giving me error
Invalid datetime format: 7 ERROR: invalid input syntax for type timestamp: "date_trunc('hour', CURRENT_TIMESTAMP)"

my table are like this

table token

id       | generated_time
196618   | 2016-10-15 01:02:48.963
196619   | 2016-10-15 01:02:50.569
196620   | 2016-10-15 01:03:12.931
196621   | 2016-10-15 02:03:17.037
196622   | 2016-10-15 02:22:55.782
196623   | 2016-10-15 02:24:57.477
196624   | 2016-10-15 03:23:00

table token_queue

id | serving_start_time      | serving_end_time        | token_id
1  | 2016-10-15 01:02:48.963 | 2016-10-15 01:03:02.69  | 196618

thank you for any suggestion.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire