I have a table where I store timeseries data: customer_id transaction_type transaction_date transaction_value 1 buy 2022-12-04 100.0 1 sell 2022-12-04 80.0 2 buy 2022-12-04 120.0 2 sell 2022-12-03 120.0 1 buy 2022-12-02 90.0 1 sell 2022-12-02 70.0 2 buy 2022-12-01…
I'm trying to implement properly a trigger function in Postgresql. The database consists of three tables, two of which are connected by a third one which is an intermediary: Table MUSICIAN (PK = id_musician) id_musician name birth death gender nationality…
I have the following table: province_id owner_id game_id resource_production valid_from valid_until static_province_id auto incremented Integer integer integer integer timestamp timestamp integer I have two indexes: test_valid_from(game_id, owner_id, valid_from, valid_until) test_valid_until(game_id, owner_id, valid_until, valid_from) Every time the production of a province…
I have two tables in mysql database subjectids id subject 11 Physics 12 Chemistry 13 Maths 14 Biology 15 History 16 Geography studentsScores id student subjectid score 1 Ahaan 11 45 2 Ahaan 12 33 3 Ahaan 13 49 4…
I have this relation in my model... $this->hasMany('AppInventory')->where('status',1) ->whereNull('deleted_at') ->where(function($query){ $query ->where('count', '>=', 1) ->orWhere(function($aQuery){ $aQuery ->where('count', '=' , 0) ->whereHas('containers', function($bQuery){ $bQuery->whereIn('status', [0,1]); }); }); }) ->orderBy('updated_at','desc') ->with('address', 'cabin'); And Sql query generated are : select * from `inventories`…
String contains words separated by spaces. How to get substring from start until first uppercase word (uppercase word excluded)? If string start with uppercase, this word should included. Search should start from secord word. First word should always appear in…
Joining 2 tables (orders, addresses) Orders contains columns delivery_address_id (contains NULL values) and invoice_address_id (does NOT contain NULL values) Addresses contains id column (does NOT contain NULL values) Primarily, the LEFT JOIN must be performed on orders.delivery_address_id. However, in the…
I want to use BINARY UUIDs as my primary key in my tables, but using my own custom functions that generates optimised UUIDs loosely based on this article: https://mariadb.com/kb/en/guiduuid-performance/ The table structure and two main functions of interest here are:…
I'm working with the following table where you can get activity from customer purchases. DateOfActivity CustomerReference Reference Line Description Receivable Amount 24/10/2022 CUST567 1 Credit Purchase 20,000 24/10/2022 CUST567 4 Credit Purchase 10,000 24/10/2022 CUST555 2 Credit Purchase 50,000 27/10/2022…
The timestamps my database returns are in this format: '2022-11-25T17:54:29.819Z' I want to do hour(timestamp) to return just the hour but get this error 'error:"function hour(timestamp with time zone) does not exist"' How do I get around this? Thanks!