On 2017/04/12 1:24 AM, Ron Barnes wrote:
Hello all,
To everyone who helped me before - thank you very much!
I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
I have to count a Date/Time field and the problem is, this field contains data
in a format I'm not sure can be counted.
I need to count all the dates in the field but the dates are a combined Date
and time in the format examples below.
My goal is to use the current Date/time ('NOW') and calculate the time
difference in days, from my DB Sourced field.
I need to capture...
Less than 1 month old
1 month old
2 months old
1 year old.....
all the way to greater than 10 years old.
Your data is in a bad format as others pointed out, and probably the
fastest solution would be to fix it in a program, however, SQLite can
fix it.
This next script will use CTE's to interpret the date, then reassemble
it as ISO8601 date format and then calculate the elapsed days since that
date.
I've used your example dates in the test, plus added a few of mine to
make sure we catch every possibility.
To understand better what is happening, you can query any of the CTE
tables (DA, DB, DC, etc.) in the main query.
Also, I do the re-interpretation to use Julianday, but with a bit of
cleverness, once you've interpreted the date constituents (CTE table DC
below) you can already calculate the elapsed days, months or years.
Have fun!
-- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
-- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 4 Parameter Count: 0
-- 2017-04-12 13:43:15.875 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TEMPORARY TABLE Volume_Information(
ID INTEGER PRIMARY KEY,
VI_Creation_Date TEXT
);
INSERT INTO Volume_Information(VI_Creation_Date) VALUES
('10/30/2015 2:28:30 AM'),
('2/13/2016 7:51:04 AM'),
('5/15/2016 12:06:24 PM'),
('10/7/2016 1:27:13 PM'),
('3/3/2017 1:7:3 PM'),
('10/1/2016 6:59:18 AM'),
('10/09/2016 11:27:13 PM');
WITH DA(ID,DT) AS (
SELECT ID, replace(replace(replace(replace(VI_Creation_Date,'
',':'),'/',':'),'AM','0'),'PM','12')||':'
FROM Volume_Information
), DB(i, k, l, c, r) AS (
SELECT DA.ID, 0, 1, DA.DT, -1
FROM DA
UNION ALL
SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1),
CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT)
FROM DB
WHERE l > 0
), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS (
SELECT DA.ID,
MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END)
FROM DA, DB
WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0
GROUP BY DA.ID
), DD(ID, ISO_DT) AS (
SELECT ID, YY||'-'||
CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'||
CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '||
CASE
WHEN HH = 12 AND AP = 0 THEN '00'
WHEN HH = 12 AND AP > 0 THEN AP
WHEN HH + AP > 9 THEN HH + AP
ELSE '0'||HH
END||':'||
CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'||
CASE WHEN SS > 9 THEN SS ELSE '0'||SS END
FROM DC
)
SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT,
printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince
FROM Volume_Information AS VI
JOIN DD ON DD.ID = VI.ID
ORDER BY VI.ID
;
-- VI.ID | VI.VI_Creation_Date |
DD.ISO_DT | DaysSince
-- ------------ | ------------------------ |
--------------------- | ---------
-- 1 | 10/30/2015 2:28:30 AM | 2015-10-30
02:28:30 | 530.4
-- 2 | 2/13/2016 7:51:04 AM | 2016-02-13
07:51:04 | 424.2
-- 3 | 5/15/2016 12:06:24 PM | 2016-05-15
12:06:24 | 332.0
-- 4 | 10/7/2016 1:27:13 PM | 2016-10-07
13:27:13 | 186.9
-- 5 | 3/3/2017 1:7:3 PM | 2017-03-03
13:07:03 | 39.9
-- 6 | 10/1/2016 6:59:18 AM | 2016-10-01
06:59:18 | 193.2
-- 7 | 10/09/2016 11:27:13 PM | 2016-10-09
23:27:13 | 184.5
DROP TABLE Volume_Information;
-- Script Stats: Total Script Execution Time: 0d 00h 00m
and 00.022s
-- Total Script Query Time: -- --- ---
--- --.----
-- Total Database Rows Changed: 7
-- Total Virtual-Machine Steps: 6304
-- Last executed Item Index: 4
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2017-04-12 13:43:15.881 | [Success] Script Success.
-- 2017-04-12 13:43:15.881 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all
DB connections during run) ------
-- [2017-04-12 13:43:15.843] APPLICATION : Script
E:\Documents\SQLiteAutoScript.sql started at 13:43:15.843 on 12 April.
-- [2017-04-12 13:43:15.881] ERROR (284) : automatic index on DB(i)
--
================================================================================================
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
