I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-21 | 2019-07-19 | 2019-07-20 | I need SumCol to have a default value as UNIX_TIMESTAMP(JobDate) + UNIX_TIMESTAMP(RecordTime) I've tried creating a virtual column but it gives me this error : **Expression of generated column 'Test2' contains a disallowed function.** that's what I tried: ALTER TABLE jobsTemp ADD SumCol TIMESTAMP AS (UNIx_timestamp(JobDate) + UNIx_timestamp(RecordTime));

|tracking_id | date_action | action_id | |------------|--------------|------------| | 1 | 2021/01/24 | 4 | | 1 | 2021/01/26 | 3 | | 2 | 2021/01/25 | 67 | | 2 | 2021/01/24 | 8 | | 4 | 2021/01/27 | 10 | | 5 | 2021/01/24 | 6 | | 4 | 2021/01/25 | 55 | Result should be: |tracking_id | date_action | action_id | |-------------|-------------|--------------------| | 1 | 2021/01/26 | 3 | | 2 | 2021/01/25 | 67 | | 4 | 2021/01/27 | 10 |

This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is the best, most efficient setup? We have a calendar event table that will quickly grow to millions of records. The schema is something like: CREATE TABLE [dbo].[CalendarEvent]( [Id] [uniqueidentifier] NOT NULL, [DtStart] [datetime] NULL, [DtEnd] [datetime] NULL, [Created] [datetime] NULL, [LastModified] [datetime] NULL, [CalendarEventType] [nvarchar](255) NULL, [CalendarId] [uniqueidentifier] NULL PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] Forget about recurring events, etc. as that doesn't bear on our problem. Most queries will be of the type: select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020' Notice no joins, etc. But we will also have some that select for individual events, and include joins: select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff' Questions and ideas: 1. Should we keep the Id as the PK, but make the start date the clustered index? 2. Should we just make an index on dtStart? 3. Should we partition by month? 4. Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries? In general, when you do your querying on a table by date range, what is the best setup for this type of table? Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.

I have a problem with T-SQL that is out of my control. Their T-SQL is failing to work with a date that was cast to **VARCHAR(10)**. They are expecting to get the default yyyy-mm-dd (the documented default style for 'date' - 21) Most of the time it works ok but it fails sometimes. I've found that it fails when the cast returns 'mmm dd yyyy' format - i.e Jun 21 2022. The **VARCHAR(10)** makes the result of the cast unusable as it truncates to 'mmm dd yyy' - i.e Jun 21 202. After some mucking around I found the following strange behaviour SELECT CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices where InvoiceNumber > -1000000 Result: Jun 12 2025 (x50,000) SELECT TOP 1CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices where InvoiceNumber > -1000000 Result: 2025-06-12 (x1) SELECT CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices Result: 2025-06-12 (x50,000) It's hard to know what other information to provide as these results are so strange. Select @@LANGUAGE returns British. SQL2019, 15.0.4410.1 (november)

I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active. I have a table like this:

group_id |   id   | type | start_date |  end_date
----------+--------+------+------------+------------
       33 | 119435 | AAA  | 2013-05-21 | 2014-05-19
       33 |  15144 | AAA  | 2013-05-21 | 2015-05-18
       33 |  29393 | AAA  | 2013-05-21 | 2016-05-23
       33 | 119437 | AAA  | 2013-05-21 | 2017-05-15
       33 |  62380 | AAA  | 2013-05-21 | 2099-12-31
       33 | 119436 | AAA  | 2013-05-21 | 2099-12-31
       33 |  27346 | AAA  | 2013-05-21 | 2099-12-31
       33 |  28529 | AAA  | 2014-05-20 | 2099-12-31
       33 | 221576 | AAA  | 2015-05-19 | 2099-12-31
       33 | 253893 | AAA  | 2016-05-24 | 2099-12-31
       33 | 251589 | AAA  | 2017-05-16 | 2099-12-31
       33 | 285245 | AAA  | 2019-01-24 | 2099-12-31
       34 | 253893 | AAA  | 2016-05-24 | 2099-12-31
       34 | 251589 | AAA  | 2017-05-16 | 2099-12-31
       34 | 285245 | AAA  | 2019-01-24 | 2099-12-31
       34 | 285246 | AAA  | 2019-05-31 | 2099-12-31

... and I need to get active counts for each of those date ranges like this:

group_id | start_date |  end_date  | active
----------+------------+------------+--------
       33 | 2013-05-21 | 2014-05-19 |      7
       33 | 2013-05-21 | 2015-05-18 |      8
       33 | 2013-05-21 | 2016-05-23 |      9
       33 | 2013-05-21 | 2017-05-15 |     10
       33 | 2013-05-21 | 2099-12-31 |     12
       33 | 2013-05-21 | 2099-12-31 |     12
       33 | 2013-05-21 | 2099-12-31 |     12
       33 | 2014-05-20 | 2099-12-31 |     11
       33 | 2015-05-19 | 2099-12-31 |     10
       33 | 2016-05-24 | 2099-12-31 |      9
       33 | 2017-05-16 | 2099-12-31 |      8
       33 | 2019-01-24 | 2099-12-31 |      8
       34 | 2016-05-24 | 2099-12-31 |      1
       34 | 2017-05-16 | 2099-12-31 |      2
       34 | 2019-01-24 | 2099-12-31 |      3
       34 | 2019-05-31 | 2099-12-31 |      4

I've tried various combinations of LAG and LEAD, with and without CTEs, but cannot come up with a solution. Is there a way to do this in a single query? If not a single query, perhaps a combination of queries in a UDF? **UPDATE** Per @jjanes comment below, I believe my source table is setup incorrectly. I think I should create the source table like this instead:

group_id |   id   | type | start_date |  end_date
----------+--------+------+------------+------------
  ... (skipped group 33) ...
       34 | 253893 | AAA  | 2016-05-24 | 2017-05-15
       34 | 253893 | AAA  | 2017-05-16 | 2019-01-23
       34 | 253893 | AAA  | 2019-01-24 | 2019-05-30
       34 | 253893 | AAA  | 2019-05-31 | 2099-12-31
       34 | 251589 | AAA  | 2017-05-16 | 2019-01-23
       34 | 251589 | AAA  | 2019-01-24 | 2019-05-30
       34 | 251589 | AAA  | 2019-05-31 | 2099-12-31
       34 | 285245 | AAA  | 2019-01-24 | 2019-05-30
       34 | 285245 | AAA  | 2019-05-31 | 2099-12-31
       34 | 285246 | AAA  | 2019-05-31 | 2099-12-31

With that change in the source data, the outcome of actives (showing only group 34 here) would be like this:

group_id | start_date |  end_date  | active
----------+------------+------------+--------
       34 | 2016-05-24 | 2017-05-15 |      1
       34 | 2017-05-16 | 2019-01-23 |      2
       34 | 2019-01-24 | 2019-05-30 |      3
       34 | 2019-05-31 | 2099-12-31 |      4

In my table subventions (grants), I created a trigger to be able to calculate the annual amount of money of a grant, using my columns subventions.début (beginning), subventions.fin (end) and subventions.montant (amount). As DATEDIFF only gives me an answer in days, I naively thought that I only had to divide it by 365 days to obtain my answer. It works if my beginning date is the first of january and the end date the 31st of December, but does not if it stars somewhere else during the year. Here is my trigger: SET NEW.montant_annuel = NEW.montant / (datediff(NEW.fin,NEW.début)/365) Here is my table and the result in the 'montant_annuel' column: If I use 365.25 (which is the "real" number of days in a year), it does not work either.

This is my data: I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from October 1st. Expected result would be all dates from October till last day of previous month. For testing purpose I replaced sysdate, 'MM' with to_date('31-DEC-18'), 'MM' Below is working but wondering if I can improve it... PER_PERIOD_START_DATE between (CASE WHEN (a.PER_MONTH_NUM = 10 and extract( year from a.PER_PERIOD_START_DATE) = 2018) THEN Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) +14 ELSE Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) END ) and trunc(to_date('31-DEC-19'), 'MM')-1 Final code: where PER_PERIOD_START_DATE between (CASE WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018) THEN Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) + 14 ELSE Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) END ) and trunc(sysdate, 'MM')-1

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster. //Get the first day and last day $dateInitial = strtotime('2018-08-21'); $dateFinal = strtotime('2019-08-21'); $final = array(); $sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity FROM ( SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing, SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production, ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting FROM ( SELECT MAX(id) AS id FROM sw_sowing WHERE status != 0 AND id_tenant = :id_tenant AND date db->prepare($sql); //get the sunday dates between two dates and bind the variables for ($i = $dateInitial; $i date("Y-m-d", $i), ':id_tenant' => 1 ]; $types = [ ':dates' => Column::BIND_PARAM_STR, ':id_tenant' => Column::BIND_PARAM_INT ]; $result = $this->db->executePrepared($statement, $values, $types); $final[] = $result->fetchAll(Phalcon\Db::FETCH_ASSOC); } } return $final; But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster. I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate. This is a question that I did before about why I use GROUP BY and MAX(id) https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql

What's the best way to create month ranges without using specific dates? For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month without updating the date. I was thinking of working with current_date, but is there a way to create a more efficient query? ``` select datetrunc('month',date) ,count(id) filter (where status='sold' and (date between (datetrunc('month','current_date - interval '1 months')) and datetrunc('month', current_date) - Interval '1 days')) / count(id) filter (where date between (datetrunc('month','current_date - interval '1 months')) and datetrunc('month', current_date) - Interval '1 days') as Mar2020_Sales_Rate ,..... as Feb2020_Sales_Rate ,. ,. ,..... as Mar2019_Sales_Rate from sales group by 1;

How to convert a string in this specific format: 'Aug 6, 2018 3:17:11 PM' to the DATE data type using the function TO_DATE()?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-01-15| 1335 | +-------------+----------+--------+ Get all Records with date passed n days and its multiple serires like below... for example n=5 SELECT * FROM invoices WHERE `due_date = DATE_ADD(CURDATE() + INTERVAL 5 days) OR due_date = DATE_ADD(CURDATE() + INTERVAL 10 days) OR due_date = DATE_ADD(CURDATE() + INTERVAL 15 days)` but i want to make it universal for any n value > **Note:** mySQL version in my machine is 5.*

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query with no conditions I noticed that BO re-formats the date to 10/15/18

I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format. Id date 1 01-07-2014 2 02-07-2014 3 03-07-2014 4 05-07-2014 5 07-07-2014 6 14-07-2014 7 18-07-2014 8 19-07-2014 9 21-07-2014 10 01-08-2014 11 02-08-2014 12 03-08-2014 On the php file $from = '01-07-2014'; $to = '02-08-2014'; I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy. I am using UPDATE successlog SET successlog.overtime ='00:00' WHERE date >= '$from' AND date <= '$to' It is not working. I tried using the key between $from and $to. This does not work either. When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format? I found this question , but it shows only how to convert timestamp without time zone to unix timestamp.

I am trying to load CSV into a MariaDB . Having issues in transforming date . Here is an example file and two tables ( emp , where dob is declared as a date column and emp_varchar , where dob is declared as varchar). What am I doing wrong here? Jane@dbserver1:~$ cat empdata.csv 100,John ,02/14/1955 200,Jane ,08/22/1980 MariaDB [emptest]> LOAD DATA LOCAL INFILE 'empdata.csv' -> INTO TABLE emp -> FIELDS TERMINATED BY ',' -> OPTIONALLY ENCLOSED BY '\"' -> LINES TERMINATED BY '\n' -> IGNORE 0 ROWS -> ( -> eid, -> ename, -> dob -> ) -> SET dob = STR_TO_DATE(dob, '%m/%d/%Y'); Query OK, 2 rows affected, 4 warnings (0.001 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 4 MariaDB [emptest]> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1265 | Data truncated for column 'dob' at row 1 | | Warning | 1411 | Incorrect datetime value: '0000-00-00' for function str_to_date | | Warning | 1265 | Data truncated for column 'dob' at row 2 | | Warning | 1411 | Incorrect datetime value: '0000-00-00' for function str_to_date | +---------+------+-----------------------------------------------------------------+ 4 rows in set (0.000 sec) MariaDB [emptest]> select * from emp; +------+-----------+------+ | eid | ename | dob | +------+-----------+------+ | 100 | John | NULL | | 200 | Jane | NULL | +------+-----------+------+ 2 rows in set (0.000 sec) MariaDB [emptest]> LOAD DATA LOCAL INFILE 'empdata.csv' -> INTO TABLE emp_varchar -> FIELDS TERMINATED BY ',' -> OPTIONALLY ENCLOSED BY '\"' -> LINES TERMINATED BY '\n' -> IGNORE 0 ROWS -> ( -> eid, -> ename, -> dob -> ) -> SET dob = STR_TO_DATE(dob, '%m/%d/%Y'); Query OK, 2 rows affected (0.001 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [emptest]> select * from emp_varchar -> ; +------+-----------+------------+ | eid | ename | dob | +------+-----------+------------+ | 100 | John | 1955-02-14 | | 200 | Jane | 1980-08-22 | +------+-----------+------------+ 2 rows in set (0.000 sec) MariaDB [emptest]>

I am using MySQL 5.7 and I have notice something which I cannot explain to myself with my current knowledge so here it goes. Is an "invalid" date 0000-00-00 considered same as NULL for a date column type with no possibilities for nullables? See the following example:

create table if not exists example
(
    id int auto_increment primary key,
    doskip date default '0000-00-00' not null
);

INSERT INTO example VALUES(1, '0000-00-00'), (2, '0000-00-00'), (3, '0000-00-00');

Having the above and running the following queries gave me the exact same count: 3 and I wonder why, is there any explanation for this?

SELECT COUNT(*) FROM example j WHERE j.doskip = '0000-00-00';
SELECT COUNT(*) FROM example j WHERE j.doskip IS NULL;

Here is a Fiddle showing this behavior, is it because of the mode?

I'm trying to calculate days between two dates. Let's say I have the date **'2005-05-25 06:04:08'** and I want to count how many days are there between that day and the current date. I tried doing:

SELECT DATE_PART('day', AGE(CURRENT_TIMESTAMP, '2005-05-25 06:04:08'::timestamp )) AS days;

But for some reason this returns 11, it's not taking into account the difference in years. How can I solve this?

I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a descending fashion. Reading through some questions, this query seems to work: SELECT COUNT(invite_code) AS counted FROM invite_table GROUP BY invite_code ORDER BY counted DESC LIMIT 10; But it doesn't consider the month. What I am looking for is to get the most frequently appearing user_code where the month is specified. Also any criticism about the table design is welcome as I have deliberately designed it such that there are repeating rows with duplicate values. I am trying to track users whose invite code is used the most in a given month, I also have codes that indicate which channel a user comes from (maybe seeing an ad in FB for example), is this a valid table design?

Table "public.invite_table"
   Column    |         Type         | Collation | Nullable | Default
-------------+----------------------+-----------+----------+---------
 user_code   | character varying    |           | not null |
 invite_code | character varying    |           |          |
 month       | character varying(3) |           | not null |
 points      | integer              |           | not null |
Indexes:
    "invite_table_pkey" PRIMARY KEY, btree (user_code)
Foreign-key constraints:
    "invite_table_user_code_fkey" FOREIGN KEY (user_code) REFERENCES user_table(user_code)

I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per patient. I appreciate your help. 0 |Patient_bed_id |Patientname| Hospital_In_date| Hospital_out_Date| |-|-|-|-| |111| Lukas| 1/1/2022| 1/31/2022| |111 | Lukas | 2/1/2022| 2/28/2022| |111| Lukas| 3/1/2022| 3/31/2022| |111| Lukas| 5/25/2022| 6/2/2022| |111| Lukas| 8/1/2022| 8/20/2022| |111| Lukas| 8/21/2022| 9/10/2022| |222| Jason| 5/1/2022| 5/3/2022| |222| Jason| 6/15/2022 | 7/11/2022| |222| Jason| 7/12/2022 | 7/26/2022| |222| Jason| 9/13/2022| 9/15/2022| |222| Jason| 9/16/2022 |9/27/2022| Final table |Patient_bed_id| Patientname| Hospital_In_date| Hospital_out_Date| |-|-|-|-|- |111| Lukas| 1/1/2022| 3/31/2022| |111| Lukas| 5/25/2022| 6/2/2022| |111| Lukas| 8/1/2022| 9/10/2022| |222| Jason| 5/1/2022| 5/3/2022| |222| Jason| 6/15/2022| 7/26/2022| |222| Jason | 9/13/2022| 9/27/2022|

I have a table that store time begin and time end of some events. I want to search in this table all even that were occuring during specific day. Here is example data: | Id | Date begin | Date End | Name | | -- | ------------------- | ------------------- | ----- | | 1 | 2024-04-18 13:30:45 | 2024-04-18 15:30:45 | 18-04 | | 2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 | | 3 | 2024-04-15 13:30:45 | 2024-04-15 15:30:45 | 15-04 | | 4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 | | 5 | 2024-04-12 13:30:45 | 2024-04-12 15:30:45 | 12-04 | | 6 | 2024-03-18 13:30:45 | 2024-03-18 15:30:45 | 18-03 | | 7 | 2024-03-15 13:30:45 | 2024-03-16 15:30:45 | 15-03 | | 8 | 2024-03-14 13:30:45 | 2024-03-15 15:30:45 | 14-03 | | 9 | 2024-03-12 13:30:45 | 2024-03-13 15:30:45 | 12-03 | If I want data for the 14/04, this is the excepted output: | Id | Date begin | Date End | Name | | -- | ------------------- | ------------------- | ----- | | 2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 | | 4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 | Here is my query:

SELECT * FROM content WHERE
    DATE_FORMAT(dateBegin, '%d/%m/%Y') = '14/04/2024'

But here is my actual ouput: | Id | Date begin | Date End | Name | | -- | ------------------- | ------------------- | ----- | | 2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 | | 4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 | | 8 | 2024-03-14 13:30:45 | 2024-03-15 15:30:45 | 14-03 | Why I get a line from the right day, but of another month ? I made a [Db Fiddle](https://www.db-fiddle.com/f/qasXqfLeEp1a6Mvi7A5Pqu/2) to show this problem. How can I fix it? Note: I'm using MariaDB 10.11.4.