extract only time from dateTime using query builder

Question

How can I extract or use only time from this trades.tradedate : "2018-08-14 06:48:24" using laravel query builder.

Output should be: 06:48:24 my function:

public function boot()
{
    Schema::defaultStringLength(191);



    view()->composer('layouts.member', function ($view) {

        $user = optional(Auth::user())->id;
        $value = DB::table('exchanges')
            ->leftJoin('trades', 'exchanges.id', '=', 'trades.exchange_id')
            ->where('trades.user_id', $user)
            ->whereRaw("? NOT BETWEEN start_time AND close_time", 'trades.tradedate')

  OR   ->where('trade_date','>=', 'exchanges.close_time')->where('trade_date', '<=', 'exchanges.start_time')

            ->get();
        $view->with('value', $value);
    });

}

Please share your code what you have tried so far for better answers. Also you can try this way - date('H:i:s') — Shreeraj
– Shreeraj, Commented Aug 19, 2018 at 9:25

Ahsan · Accepted Answer · 2018-08-19 10:30:23Z

2

I am not sure, but you can try something like this:

public function boot()
{
    Schema::defaultStringLength(191);



    view()->composer('layouts.member', function ($view) {

        $user = optional(Auth::user())->id;
        $value = DB::table('exchanges')
            ->leftJoin('trades', 'exchanges.id', '=', 'trades.exchange_id')
            ->where('trades.user_id', $user)
            ->select(DB::raw('TIME(trades.tradedate) AS trade_date'))
            ->whereRaw("? NOT BETWEEN start_time AND close_time", 'trade_date')

            ->get();
        $view->with('value', $value);
    });

}

answered Aug 19, 2018 at 10:30

Ahsan

1,3694 gold badges15 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KINNARI Over a year ago

this is good idea but, its giving me result as trade_date only. @Ahsan

Erubiel Over a year ago

i think you can override it by using TIME(trades.tradedate) AS tradedate ... but somehow i don't see it as a good practice

Erubiel Over a year ago

Or maybe you were referring to the fact that it just gives you this column, yeah by default it goes and select *, but if you specify one select, you'll have to specify every column, or use ->selectRaw('*')

Ahsan Over a year ago

Which result are you getting as trade_date?

Илья Зелень · Accepted Answer · 2018-08-19 08:50:48Z

1

You can use Carbon for parsing and get time from date.

For getting time from your model you can add method to your model:

public function getTime ()
{
    return \Carbon\Carbon::parse($this->my_date)->toTimeString();
}

Or you can add accessor(getter fro attribute) for your model:

public function getMyDateAttribute($date)
{
    return \Carbon\Carbon::parse($date)->toTimeString();
}

More about accessors, Carbon documentation.

answered Aug 19, 2018 at 8:50

Илья Зелень

8,2783 gold badges48 silver badges59 bronze badges

2 Comments

KINNARI Over a year ago

I think i can not pass carbon.. let me post here my query

KINNARI Over a year ago

i have posted my function in question, trades.tradedate require to extract time.

Collectives™ on Stack Overflow

extract only time from dateTime using query builder

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related