postgresql – Postgres – Zero-time part of a timestampz column for all rows in a table

I have a table filled with a column timestampz Values. I want to zero all these values ​​so that they keep their date portion but have UTC time values.

For example:

start_date

2020-01-01 20:50:10.539000 => 2020-01-01 00:00:00.000000
2020-01-01 05:00:00.000000 => 2020-01-01 00:00:00.000000
2020-01-01 05:00:00.000000 => 2020-01-01 00:00:00.000000
2020-01-01 05:00:00.000000 => 2020-01-01 00:00:00.000000
2019-12-01 23:19:52.801000 => 2019-12-01 00:00:00.000000
2019-12-01 22:40:09.103000 => 2019-12-01 00:00:00.000000
2019-12-01 22:35:14.684000 => 2019-12-01 00:00:00.000000
2019-12-01 22:15:41.539000 => 2019-12-01 00:00:00.000000

I tried to use an inner selection, but I'm not sure if I should use a temporary table.

Any recommendations regarding which approach I should take would be very grateful.

Merge returned rows with the same columns in SQL Server

I have an SQL query like this:

  select e.activa, e.id_entidad ,e.Alias 'Empresa' ,dir.calle 'Dirección' ,dir.desc_adicional 'Dirección adicional' ,dir.codigo_postal 'Código postal' ,loc.localidad 'Población' ,p.provincia 'Provincia' ,e.telefono 'Teléfono' ,e.cif 'CIF' ,e.web 'Web' ,
  CASE WHEN d.contacto_entidad = 1 THEN d.nombre  ELSE ' ' END AS 'Nombre - Contacto Sector',
  CASE WHEN d.contacto_entidad = 1 THEN d.email  ELSE ' ' END AS 'E-mail - Contacto Sector',
  case when d.rep_legal_entidad = 1 then d.nombre else ' ' end as 'Nombre - Responsable Legal',
  case when d.rep_legal_entidad = 1 then d.email else ' ' end as 'E-mail - Responsable legal' 
  from entidades e left outer join direcciones as dir on e.dom_comercial = dir.id_direccion 
  and( dir.activa = 'S' and e.id_tipoentidad = 1) 
  left outer join Directivos as d on d.id_entidad = e.id_entidad
  and (d.contacto_entidad = 1 or d.rep_legal_entidad = 1) 
  and d.activa = 'S' 
  left outer join localidades as loc on loc.id_localidad = dir.id_localidades
  and loc.activa = 'S'
  left outer join Provincias as p on p.id_provincia = loc.id_provincia
  and p.activa = 'S'  where e.activa = 'S' order by e.nombre

The problem I have is that it returns records in which all fields are the same, but the fields in white could be combined with the fillings, as the picture looks like
Enter image description here

In the picture, the crossed out fields are the same at the top and bottom.
How would that work?

postgresql – Select rows within the group before the condition is met

I would like to select rows within each group after a certain time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group.

| traj_id | timestamp  | height | lat   | Lon    |
|---------|------------|--------|-------|--------|
| 1       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 1       | 2006-01-02 | 9      | 34.56 | -70.88 |
| 1       | 2006-01-03 | 0      | 34.56 | -70.89 |
| 1       | 2006-01-04 | 5      | 34.57 | -70.90 |
| 1       | 2006-01-05 | 20     | 34.60 | -70.94 |
| 2       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 2       | 2006-01-02 | 5      | 34.55 | -70.82 |
| 2       | 2006-01-03 | 3      | 34.54 | -70.89 |
| 2       | 2006-01-04 | 0      | 34.53 | -70.90 |
| 2       | 2006-01-05 | 10     | 34.51 | -70.95 |
| 3       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 3       | 2006-01-01 | 9      | 34.56 | -70.75 |
| 3       | 2006-01-01 | 11     | 34.54 | -70.90 |
| 3       | 2006-01-01 | 15     | 34.60 | -70.95 |
| 3       | 2006-01-01 | 30     | 34.67 | -70.90 |

I want to filter the data to get the rows within each group whose height is greater than zero. But it's not that easy to just remove the zeros with one WHERE Statement. I want to keep all the rows before the group reaches zero for the first time:

| traj_id | timestamp  | height | lat   | Lon    |
|---------|------------|--------|-------|--------|
| 1       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 1       | 2006-01-02 | 9      | 34.56 | -70.88 |
| 2       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 2       | 2006-01-02 | 5      | 34.55 | -70.82 |
| 2       | 2006-01-03 | 3      | 34.54 | -70.89 |
| 3       | 2006-01-01 | 10     | 34.55 | -70.80 |
| 3       | 2006-01-01 | 9      | 34.56 | -70.75 |
| 3       | 2006-01-01 | 11     | 34.54 | -70.90 |
| 3       | 2006-01-01 | 15     | 34.60 | -70.95 |
| 3       | 2006-01-01 | 30     | 34.67 | -70.90 |

In this example, groups 1 and 2 are filtered and the rows are removed to zero.

I know that I can do something like this:

SELECT *
FROM trajectory_data
GROUP BY traj_id
HAVING height > 0

However, this query preserves rows that I do not necessarily want to have (that is, rows where the group has already reached zero), and I do not intend to aggregate the data, just select rows. Is there another way to tackle this?

SQL Server – Returns rows that are linked only to specific row types in another table

Untested (seemed to be a technical problem with the violin), but you may be looking for the predicate Exist

SELECT p.*
FROM person as p
WHERE EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE objectType IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

TO EDIT:
With info from the comment it becomes clear that you want people who have all 3 object types (I have not accepted one). There is no ALL quantifier in SQL (as opposed to EXISTS). However, it is possible to perform the transformation:

ALL x:p(x) <=> NOT EXISTS x:NOT x 

which as you noted in the commentary would lead to:

SELECT p.*
FROM person as p
WHERE NOT EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE objectType NOT IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

Another possibility is to count which persons have exactly 3 objects under the 3:

 SELECT p.rowkey
 FROM person as p
 JOIN link as l
     ON p.objectId = l.personObjectId
 JOIN thing as t
     ON t.objectID = l.thingObjectId
 WHERE objectType IN ('fork','knife','eraser')
 GROUP BY p.rowkey
 HAVING COUNT( DISTINCT objectType ) = 3;

These are the two most commonly used techniques for relational division I know that from.

sql server – Retrieves the CreatedAt timestamp when the number of rows equals a specified number

You could try it

SELECT
  id
  , createdAt
FROM (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
) AS S
WHERE rnk = 8
;

The inner SELECT groups the records after OtherId, orders and orders them made in, The outer one SELECT selects the record after a specific number.

The mentioned CTE (Common Table Expression) @Akina would pull up the subselection:

WITH
S (id, createdAt, rnk) AS (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
)
SELECT
  id
  , createdAt
FROM S
WHERE rnk = 8
;

Do not see much difference in this case. However, the technique helps a lot with more complex settings because it allows you to extend the instructions and is usually easier to read.

See it in action: db <> violin

Please comment if and how this requires customization / further details.

Excel Array Formula (or VBA Macro) to match a parent record with multiple related rows

Specify a table in Excel that looks like this in cells A1 through B12:

item    parent
item1   
item2   
item3   item1
item4   item1
item5   item2
item6   item3
item7   item4
item8   item5
item9   item1
item10  item2
item11  item4

I want to find one or more Excel formulas (I like to use array formulas) that can be used to generate the following column, which contains a concatenation of all related children (for example, item1 children are items 3, 4) and 9) parent.

children
item3, item4, item9
item5, item10
item6
item7, item11
item8
none
none
none
none
none
none

If it is not possible in the formula, the next best thing would be a macro, but I want the macro to be applied to a ListObject table that refers to column headings by name, as opposed to ranges (for example, A1: A12 ). I have a large amount of data that will change over time (hence the desire to use the spreadsheet functionality as opposed to fixed ranges).

Lists – What's the best way to group / ungroup rows in a data table?

I struggle with a good solution for grouping / ungrouping rows in a data table. We initially implemented drag-and-drop, but it was a nightmare and a nightmare for end users because of third-party library restrictions.

I came up with a different method, which I hope will be easier to apply, but I'm still not 100% satisfied with it. Take a look at the demo below and let me know how to improve it.

Some articles on my list:

  • Make it clearer what a click on the checkbox will do. Maybe a edit mode or a tooltip?
  • Somehow, tweak the way the "Group Items" buttons appear.

Another idea is to mimic how to group / ungroup elements with a combination Shift + select items> right-click> group / ungroup and keyboard shortcuts. However, since it's a browser app that also supports touch devices AND is targeted at an older demographic, I'm not sure if this is a good idea.

YOUTUBE DEMO: Group and ungroup rows in the data table Demo

Find rows with a specific regular expression format in Oracle

I have a table like this:

  • Table 1: col_number number

I need to find the records in this column that have the following format:

(up to 23 digits).(up to 3 digits )

I mean 23 digits before the . and 3 digits after. I use regular_expression as follows:

select *
from Table1
where REGEXP_LIKE(description, '(0-9){23}-.(0-9){3}'));

I have to somehow set minimum / maximum for the number of digits.

but it does not give me the desired answer.