SQLite Forum

SQL query help for between times and grouping times
Login
> 24 seems to represent 12nooon. Can you explain which part of the substitution does that?

The important bit of that CTE is this select:

```
    SELECT EventDate, EventTime,
           CAST(substr(EventTime,0,instr(EventTime,':')) AS INT)+
           CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

```

of which the third field is this:

```
CAST(substr(EventTime,0,instr(EventTime,':')) AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
```

To unpack it I will start from the inside and use this time as the example: " 8:12:34 PM"

This section: ......, instr(EventTime,':').....   
Returns the index of the first colon character in EventTime. So for our example time, that will be 2 (Since it is Zero-based, so at position Zero there is a space, at position 1 there is an 8 and at position 2 there is the colon, which is returned). This makes the statement resolve to:

```
CAST(substr(EventTime,0, 2 ) AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
```

This section: ......substr(EventTime,0, 2 ).....   
Returns the part of the string starting at character Zero and includes the next 2 characters (i.e. the number of characters to include is deduced previously from the index of the colon). Note that this also elegantly handles the problem of the leading space. This makes the statement resolve to:

```
CAST(' 8' AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
```

The CAST simply changes ' 8' to the Integer value 8. This makes the statement:

```
8 + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
```

The CASE checks if the EventTime ends with a PM, in which case it adds 12, otherwise it adds nothing (0). Since our example does end with a PM, it resolves to this:

```
8 + 12
```

which returns the hour correctly as: 20

The problem that arose then was that 12 AM resolved by the same steps to 12 + 0 (which is 12 and is wrong, it should be Zero-hour/midnight) and 12 PM resolved to 12 + 12 which is 24 - wrong again, it should be 12.

So to fix that, we need a modulo 12 in the statement because 12 % 12 = 0, which means 12 AM resolves to 12 % 12 + 0 which gives 0 correctly, and 12 PM resolves to 12 % 12 + 12 which gives 12 correctly.

Final statement thus becomes: 

```
CAST(substr(EventTime,0,instr(EventTime,':')) AS INT) % 12 + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
```

Hope that answers the question!