Providing Wrong date

[Nihongo^]

Hy I am trying to convert 2023-07-08 16:25:06 into months days and years like

2020-07-07 16:25:06 = 3 years 3 days ago

But the script gave me WRONG date, I entered the data today 2023-07-08 16:25:06 but saying 11 Months AGO

is it a problem in the Code or in the database ??

Code Select Expand

function GetTimeFormat2(CreatedAt) {
 
    local currentTime = date();
    local createdYear = CreatedAt.slice(0, 4).tointeger();
    local createdMonth = CreatedAt.slice(5, 7).tointeger();
    local createdDay = CreatedAt.slice(8, 10).tointeger();

    local diffYears = currentTime.year - createdYear;
    local diffMonths = currentTime.month - createdMonth;
    local diffDays = currentTime.day - createdDay;

    if (diffMonths < 0) {
        diffYears--;
        diffMonths += 12;
    }
    if (diffDays < 0) {
        diffMonths--;
        diffDays += ::daysinmonth(createdYear + diffYears, createdMonth + diffMonths);
    }

    local timeString = "";

    if (diffYears > 0) {
        timeString += diffYears + " Year" + (diffYears > 1 ? "s" : "") + " ";
    }
    if (diffMonths > 0) {
        timeString += diffMonths + " Month" + (diffMonths > 1 ? "s" : "") + " ";
    }
    if (diffDays > 0) {
        timeString += diffDays + " Day" + (diffDays > 1 ? "s" : "") + " ";
    }

    if (timeString == "") {
        timeString = "Just now";
    } else if (diffYears == 0 && diffMonths == 0 && diffDays == 0) {
        timeString = "Today";
    } else {
        timeString += "ago";
    }

    return timeString;
}

Function to retrieve data

Code Select Expand

b = b + " \n" + GetSQLColumnData(q, 0) + " " + GetTimeFormat2(GetSQLColumnData(q, 1))

[2b2ttianxiu]

You cannot use sqlite include timestamp, it cannot convert to number
You must change column data tag (numeric default 0)
and insert or update data, you use 'time().tointeger()' to update this data

[Nihongo^]

You cannot use sqlite include timestamp, it cannot convert to number
You must change column data tag (numeric default 0)
and insert or update data, you use 'time().tointeger()' to update this data

did you mean ?
 Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

can you please explain little bit ?

[habi]

change

Code Select Expand

local diffMonths = currentTime.month + 1- createdMonth;because squirrel date() returns month as 0-11 (not as 1 - 12) as seen http://squirrel-lang.org/squirreldoc/stdlib/stdsystemlib.html?highlight=date#date

[Nihongo^]

change

Code Select Expand

local diffMonths = currentTime.month + 1- createdMonth;because squirrel date() returns month as 0-11 (not as 1 - 12) as seen http://squirrel-lang.org/squirreldoc/stdlib/stdsystemlib.html?highlight=date#date

Thank you so much habi its works. Will gives you credits

[2b2ttianxiu]

You cannot use sqlite include timestamp, it cannot convert to number
You must change column data tag (numeric default 0)
and insert or update data, you use 'time().tointeger()' to update this data

did you mean ?
 Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

can you please explain little bit ?

You can use squirrel 'time' function insert or update your time data