Tag: datetime

Select on value change

datetime gaps-and-islands mysql select sql

I have a table that looks like this in a MySQL database: I would like to select all lines where the Batch_Num is different from the previous value including the first one: Is there a keyword maybe to access the previous line to compare the to the current line? Or some other way to compare a line to the line

SQL column default value set using row wise operations

database-design date-arithmetic datetime mysql sql

Is it possible to set a default value in a SQL column to be consisted of other columns? For example, in the table below is it possible to have the EndDate default to StartDate + NoOfDays? So when …

Get previous months first and last date

datetime java oracle sql where-clause

I need to query last month data from a database. So I need the first and last date of previous month to be able to make the “between” SQL query. First date starts always from 1 of course, …

How to sort and group data in request?

datetime greatest-n-per-group postgresql sql subquery

I have got table. It have some duplicates in purchaseNumber. I need select from this table only newest records for processing. In other words I need to: group elements by purchaseNumber sort them by docPublishDate and select newest. if any of elements in group is have status parsing_status true (suppose it wa…

SQL: Select certain rows with window functions in MySQL/MariaDB

datetime mysql sql subquery window-functions

My problem can be simplified to the following example: Now I’m looking for an sql statement which selects all rows (ordered by dfrom) having a dfrom not between the dfrom and dto of any previously selected row, possibly using window functions to get informations on other rows, like this: resulting in: C…

How to set pdo timezone at connection

datetime mysql pdo php sql

I have this code where I connect to database and the code is working properly. However the now() function of MySQL is out of sync with PHP set default time zone function. I am comparing the PHP date with MySQL now. Therefore, I wanted to set the database time zone as well. I wanted to set the database time up…

Oracle – Count based on previous and next column

datetime oracle sql subquery where-clause

I’ve got a rather unusual question about some database query with oracle. I got asked if it’s possible to get the number of cases where the patient got a resumption on the same station they were discharged from within 48 / 72 hours. Consider the following example: Case Station From To 1 Stat_1 202…

Using SQL to group consecutive items that share a common status (dummy data included)

datetime gaps-and-islands recursive-query sql sql-server

Given a table that has sometimes repeated statuses within a group (in this case “vehicles”), I want to consolidate those statuses into a single row and aggregate status_seconds. The data looks like this (I’ll include some TSQL below to select dummy data into a temp table to make it easy to w…

Order by with formatted dates, doing it right?

aggregate-functions datetime sql sql-order-by sql-server

I wanted a mere sanity check because I have a slight feeling I am doing it all wrong. I stripped down the use case to a very simple sample. When formatting a date I need to cast the data in the order …

Find closest match to value in another table

datetime lateral-join postgresql postgresql-13 sql

I have a table_a with many rows and columns for each timestamp in PostgreSQL 13. I’m trying to find the row where the value in column X is closest to a benchmark value obtained from another table. This second table has only a single benchmark value for each timestamp. For each timestamp, I need to retur…