I know this isn't at all the ideal way to do this, but I solved this myself using a compensator function in my C# application that was pulling the SQLite values. It actually occurred to me after I wrote this that I could've just re-done the date subtraction in C# and over-wrote my Age field! But since I just needed to modify the hours (and days, if hours was 0 and it was a DST date) for the given condition, I just used this.
So I have a class that will provide a DataTable of results based on the query I provide to a public static string variable in that class and another function I call. Then, I call BindTable() to bind that table to the ListView in my WPF app to display the information.
I pull my DataTable in by calling ADOClass.adoDataTable. Once I have the DataTable, I just iterate through the rows, store the Created Date as a variable, and the Age string (which I use the function to update as necessary) as a variable. If the Created Date meets the .IsDaylightSavingTime() condition, it has to get an hour subtracted. If the Age was say, 0 hours (and some odd minutes) old, we have to set the days back one day, and the hours to 23.
private void BindTable()
{
DataTable dt = ADOClass.adoDataTable;
if (!Oracle_DAL.ConnTest()) // if no Oracle connection, SQLite is running and it needs DST correction
{
int oldHours = 0;
int newHours = 0;
int oldDays = 0;
int newDays = 0;
string ageCell = String.Empty;
string hoursString = String.Empty;
string daysString = String.Empty;
string crDate = String.Empty;
if (dt != null && dt.Rows != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
crDate = dr["CREATED_DATE"] != null ? dr["CREATED_DATE"].ToString() : String.Empty;
if (!String.IsNullOrEmpty(crDate))
{
DateTime createdDate = DateTime.Parse(crDate);
if (createdDate.IsDaylightSavingTime())
{
ageCell = dr["AGE"] != null ? dr["AGE"].ToString() : String.Empty;
if (!String.IsNullOrEmpty(ageCell))
{
hoursString = ageCell.Split(',')[3];
hoursString = hoursString.TrimStart(' ');
oldHours = int.Parse(hoursString.Split(' ')[0]);
if (oldHours == 0)
{
newHours = 23;
daysString = ageCell.Split(',')[2];
daysString = daysString.TrimStart(' ');
oldDays = int.Parse(daysString.Split(' ')[0]);
oldDays--;
newDays = oldDays;
ageCell = ageCell.Replace(daysString, newDays.ToString() + " days");
dr["AGE"] = ageCell;
}
else
{
oldHours--;
newHours = oldHours;
}
dr["AGE"] = ageCell.Replace(hoursString, newHours.ToString() + " hours");
}
}
}
}
}
}
}
lstData.DataContext = dt; // binds to my ListView's grid
}
(NOTE: I realized later that, because I tested this function on a non-DST day, and this was meant to fix issues with DST dates, that when it becomes DST again, I believe things will change. I think I'd end up having to add an hour to my results for non-DST dates to make it work, at that point. So this would have to have an additional check if DateTime.Now conforms to .IsDaylightSavingTime() in order to know what to do. I may re-visit this post at that time.)
And in case anyone is curious.....
Here is the full query I was running in Oracle:
SELECT CREATED_DATE, (SYSDATE - CREATED_DATE) AS TOTALDAYS,
FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12) || ' years, '
|| (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) -
(FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12)) * 12) || ' months, '
-- we take total days - years(as days) - months(as days) to get remaining days
|| FLOOR((SYSDATE - CREATED_DATE) - -- total days
(FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) - -- years, as days
-- this is total months - years (as months), to get number of months,
-- then multiplied by 30.416667 to get months as days (and remove it from total days)
FLOOR(FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12)))
|| ' days, '
-- Here, we can just get the remainder decimal from total days minus
-- floored total days and multiply by 24
|| FLOOR(
((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
)
|| ' hours, '
-- Minutes just use the unfloored hours equation minus floored hours,
-- then multiply by 60
|| ROUND(
(
(
((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
) -
FLOOR((((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24))
)*60
)
|| ' minutes'
AS AGE FROM MyTable`
And this is my final, full query for SQLite from my application:
private static readonly string mainqueryCommandTextSQLite = "SELECT " +
"CREATED_DATE, " +
" (julianday('now') - julianday(CREATED_DATE, 'utc')) AS TOTALDAYS, " +
// " (((julianday('now') - julianday(CREATED_DATE))/365)*12) || ' total months, ' || " +
// " ((CAST ((julianday('now') - julianday(CREATED_DATE))/365 AS INTEGER))*12) || ' years as months, ' || " +
// Provide years, months
" CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER) || ' years, ' || " +
" CAST (((((julianday('now') - julianday(CREATED_DATE, 'utc'))/365)*12) - (CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER)*12)) AS INTEGER) || ' months, ' " +
// Provide days
"|| ((CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER) - " + // total number of days
" (CAST ((julianday('now') - julianday(CREATED_DATE, 'utc'))/365 AS INTEGER)*365) ) -" + // years in days
" CAST((30.41667 * ((CAST ((((julianday('now') - julianday(CREATED_DATE, 'utc'))/365)*12) AS INTEGER)) - ((CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) / 365 AS INTEGER)) * 12))) AS INTEGER)) " + // days of remaining months using total months - months from # of floored years * (365/12)
" || ' days, ' " +
// BUG: These next two do not get accurate hours during DST months (March - Nov)
// This gives hours
"|| CAST ((((julianday('now') - julianday(CREATED_DATE, 'utc')) - " +
" CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) AS INTEGER) " +
// This gives hours.minutes
//"|| (((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) " +
// This gives days.hours, but taking the decimal and multiplying by 24 to get actual hours
// gives an incorrect result
//"|| ((" +
// "(0.0 + strftime('%S', 'now', 'localtime') " +
// "+ 60*strftime('%M', 'now', 'localtime') " +
// "+ 24*60*strftime('%H', 'now', 'localtime') " +
// "+ 24*60*60*strftime('%j', 'now', 'localtime')) - " +
// "(strftime('%S', CREATED_DATE) " +
// "+ 60*strftime('%M', CREATED_DATE) " +
// "+ 24*60*strftime('%H', CREATED_DATE) " +
// "+ 24*60*60*strftime('%j', CREATED_DATE)) " +
// ")/60/60/24) " +
"|| ' hours, ' " +
// Provide minutes
"|| CAST (ROUND(((((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24) - " +
"(CAST((((julianday('now') - julianday(CREATED_DATE, 'utc')) - CAST((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER)) * 24) AS INTEGER)))*60) AS INTEGER)" +
"|| ' minutes' " +
" AS AGE FROM MyTable";
And new screenshot, showing everything matching (except total days, which I can alter by just subtracting 1/24 from in my C# function and update the same way, for the DST dates):
UPDATE
Since Wernfried found 2 queries in SQLite that negate the need for this function, I'm going to accept that answer for how to really solve this issue:
For Oracle -
SELECT (SYSDATE - CREATED_DATE) FROM MyTable
or using to_date format syntax is good for getting the days.hours, and doing conversions. Taking the decimal portion and multiplying by 24 is good for hours and is independent of DST, like I wanted. See above for full query I used for formatting into years, months, days, hours, and minutes.
For SQLite -
As Wernfried found, either of these will work:
• julianday('now', 'localtime') - julianday(CREATED_DATE)
• julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')
And this avoids the need for my function above.
If you use:
• julianday('now') - julianday(CREATED_DATE, 'utc')
like I have in the earlier code, above, then you would need my DST compensator function, farther above.
