SELECT CASE WHEN DATEDIFF considering hours, minutes and seconds

Asked

Viewed 65 times

1

I created the query so that when a plan expires, the output 'Date' will print 'Expired' and if it has not yet expired, print 'Current', but I need to take into account also the time(s), minute(s) and second(s) of the expiration date, because it should match the date of registration of the client’s plan in the database.

What is happening is that if the date is equal to 'today', but it has not yet fully expired taking into account the time(s), minute(s) and second(s), the return is 'Expired' in the same way, and it could not be so.

What I’ve done as far as I can, if the expiration date is longer than NOW(), is working as far as I know from the tests I’ve done:

    $sql_plano = "SELECT 
                        *,
                        (
                        SELECT 
                            `nome` 
                        FROM 
                            `plano_pacote` 
                        WHERE 
                            `token` = `ps_token` 
                        LIMIT 
                            1
                        ) AS `plano_nome`,
                        (
                        SELECT
                            CASE
                                WHEN DATEDIFF(`data_expira`, NOW()) < 0 THEN 'Expirado' 
                            ELSE 
                                'Vigente'
                        END) AS `Data`
                    FROM 
                        `plano_usuario` 
                    WHERE 
                        `user_token` = '%s' 
                    AND 
                        `status` = 'A' 
                    ORDER BY 
                        `data_cad` 
                            DESC 
                    LIMIT 
                        1
                    ;";

The field data_expira is the type DATETIME, and registration is for '2019-05-10 23:51:37'

mysql

  • But DATEDIFF is not for hours and minutes. If it is to consider have to use TIMEDIFF. This sort of thing usually resolves with a careful reading of the manual.

  • Reverti your edit, if you want to post the solution, use the bottom field, answers, and mark as accepted later.

  • @Bacco hi, thanks, I was editing the question when you guided me, I found the references on the mysql site and I will study more, I’m now starting to delve into sql, thanks beast.

  • Okay, look at the history at https://answall.com/posts/383064/revisions. to copy your solution and paste it into the appropriate field (the solution goes below, in the answers, and not in the body of the question)

  • Thanks for the tip✨

1 answer

0


Resolve by changing DATEDIFF to TIMEDIFF according to the references on the Mysql website.

    $sql_plano = "SELECT 
                    *,
                    (
                    SELECT 
                        `nome` 
                    FROM 
                        `plano_pacote` 
                    WHERE 
                        `token` = `ps_token` 
                    LIMIT 
                        1
                    ) AS `plano_nome`,
                    (
                    SELECT
                        CASE
                            WHEN DATEDIFF(`data_expira`, NOW()) < 0 THEN 'Expirado' 
                        ELSE 
                            'Vigente'
                    END) AS `Data`
                FROM 
                    `plano_usuario` 
                WHERE 
                    `user_token` = '%s' 
                AND 
                    `status` = 'A' 
                ORDER BY 
                    `data_cad` 
                        DESC 
                LIMIT 
                    1
                ;";

Changed to:

    $sql_plano = "SELECT 
                    *,
                    (
                    SELECT 
                        `nome` 
                    FROM 
                        `plano_pacote` 
                    WHERE 
                        `token` = `ps_token` 
                    LIMIT 
                        1
                    ) AS `plano_nome`,
                    (
                    SELECT
                        CASE
                            WHEN TIMEDIFF(`data_expira`, NOW()) < 0 THEN 'Expirado' 
                        ELSE 
                            'Vigente'
                    END) AS `Data`
                FROM 
                    `plano_usuario` 
                WHERE 
                    `user_token` = '%s' 
                AND 
                    `status` = 'A' 
                ORDER BY 
                    `data_cad` 
                        DESC 
                LIMIT 
                    1
                ;";

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.