SQLite vs. Oracle-calcul des différences de date - heures

je me demande si quelqu'un a vu cela, et s'il y a une solution, ou si Je ne fais juste pas quelque chose de bien. J'essaie d'obtenir la différence d'heures entre maintenant et une "date créée" dans un enregistrement de base de données - pas essayer d'obtenir le nombre total d'heures, mais les heures qui restent après que vous vous débarrassez du nombre total de jours, de sorte que vous pouvez produire que quelque chose est x jours, x heures Vieux.

Initiales Givens

utilisons un SYSDATE , ou " maintenant", de 12/6/2016 6:41 PM .

disons que j'ai une table Oracle et une table SQLite que nous appellerons MyTable . J'y ai un champ CREATED_DATE avec des dates enregistrées à l'heure locale:

CREATED_DATE
------------
1/20/2015 1:35:17 PM
6/9/2016 3:10:46 PM

les deux tables sont identiques, sauf qu'elle est de type DATE dans Oracle, mais en SQLite, vous devez stocker une date comme une chaîne de caractères avec un format 'AAAA-MM-JJ HH:mm:ss'. Mais les valeurs de chaque table sont les mêmes.

je commence obtenir la différence totale de jours entre" maintenant " et les dates. Je peux soustraire les jours entiers des jours décimaux et obtenir les heures dont j'ai besoin.

Total Des Jours-Oracle

si je fais cela dans Oracle, pour me donner la différence de jours:

SELECT (SYSDATE - CREATED_DATE) FROM MyTable

je reçois 686.211284... pour la première et 180.144976... pour le second.

Total Jours-SQLite

si je fais cela en utilisant SQLite pour me donner la différence de jours totaux, le premier est assez proche, mais le second est vraiment hors:

SELECT (julianday('now') - julianday(CREATED_DATE, 'utc')) FROM MyTable

je reçois 686.212924.... pour la première et 180.188283... pour le second.

Problème

j'ai ajouté 'utc' sur la requête SQLite parce que je sachez que julianday() utilise GMT. Sinon, les heures étaient d'environ 6 heures. Le problème est qu'ils sont maintenant de 1 heure, mais pas tout le temps. Le premier résultat donne le nombre d'heures de différence: 5, dans les deux cas:

.211284 x 24 = 5.07 hours
.212924 x 24 = 5.11 hours

quand j'abaisse ces valeurs, ça me donne le résultat dont j'ai besoin.

Avec cette seconde, cependant, voici ce que j'obtiens:

.144976 x 24 = 3.479 hours
.188283 x 24 = 4.519 hours

une énorme différence - une heure de différence! N'importe qui peut aider à comprendre pourquoi c'est, et si il y a un moyen de réparer/faire précis?

obtenir les heures

C'est le code que j'utilise pour obtenir les heures. J'ai confirmé les heures que j'obtiens lorsque j'utilise Oracle sont correctes à l'aide d'une calculatrice pour vérifier. Pour cela, j'utilise:

SELECT FLOOR(((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24) FROM MyTable

j'essaie actuellement D'obtenir les heures en SQLite en utilisant une configuration similaire:

(((julianday('now') - julianday(CREATED_DATE, 'utc')) - 
CAST ((julianday('now') - julianday(CREATED_DATE, 'utc')) AS INTEGER))*24)

j'ai quitté le" plancher", ou la fonte entière, du résultat de SQLite, pour l'instant, sur le but. Les deux requêtes prennent essentiellement le nombre total de jours moins le nombre total de jours entier pour obtenir le reste décimal (qui est la partie d'un jour qui représente des heures) et le multiplie par 24.

il est drôle, cependant, parce que j'utilise la même requête ci-dessus pour les heures entières moins une version moulée de celui-ci pour les heures entières, laissant la partie décimale pour on multiplie ça par 60 et ça ressort parfaitement pendant les minutes.

Capture d'écran: à Côté de Comparaison

cela a été pris à 12/6/2016 7:20 PM , avec SQLite montré dans mon application sur la gauche, Oracle query étant fait dans Oracle SQL Developer sur la droite:

enter image description here

1
demandé sur vapcguy 2016-12-07 03:36:03
la source

3 ответов

en fait, vous avez oublié une information importante: quelle valeur considérez-vous comme étant correcte? Devez-vous tenir compte des heures de clarté ou non?

commencer par Oracle :

je suppose que le type de données de la colonne CREATED_DATE est DATE . SYSDATE renvoie aussi une valeur DATE . La valeur DATE ne contient aucune information sur les fuseaux horaires (c'est-à-dire les réglages de L'heure avancée).

en supposant maintenant est 2016-12-06 06: 00: 00:

SELECT 
   TO_DATE('2016-12-06 06:00:00','YYYY-MM-DD HH24:MI:SS') 
   - TO_DATE('2016-06-09 06:00:00','YYYY-MM-DD HH24:MI:SS') 
FROM dual;

retourne exactement 180 jours.

si vous devez tenir compte de L'heure D'été, vous devez utiliser le type de données TIMESTAMP WITH TIME ZONE (ou TIMESTAMP WITH LOCAL TIME ZONE ), voir l'exemple suivant:

SELECT 
   TO_TIMESTAMP_TZ('2016-12-06 06:00:00 Europe/Zurich','YYYY-MM-DD HH24:MI:SS TZR') 
   - TO_TIMESTAMP_TZ('2016-06-09 06:00:00 Europe/Zurich','YYYY-MM-DD HH24:MI:SS TZR') 
FROM dual;

résultat: +180 01:00:00.000000 , soit 180 jours et 1 heure.

cela dépend de votre condition que vous devez utiliser. En général, je recommanderait d'utiliser TIMESTAMP , resp. TIMESTAMP WITH TIME ZONE plutôt que DATE , parce que là vous pouvez simplement utiliser extrait (datetime) pour obtenir les heures et vous n'avez pas à jouer avec FLOOR et des trucs du genre:

 SELECT 
    EXTRACT(HOUR FROM SYSTIMESTAMP - CREATED_DATE) AS diff_hours 
FROM MyTable;

Note, LOCALTIMESTAMP retourne un TIMESTAMP valeur, utiliser SYSTIMESTAMP , resp. CURRENT_TIMESTAMP pour obtenir l'heure courante comme valeur TIMESTAMP WITH TIME ZONE .

maintenant considérer SQLite :

mise à Jour

en fait julianday('now') - julianday(CREATED_DATE, 'utc') donne un résultat correct - ou appelons-le " résultat précis " . Il prend en compte les changements de jour. Par exemple, la différence de '2016-10-31 00:00:00' - '2016-10-30 00:00:00' (Européenne fois) est de 25 heures - pas 24 heures!

maintenant, vous aimez ignorer les changements D'heure dans votre calcul. Pour Oracle ce fut simple, utilisez DATE ou TIMESTAMP types de données au lieu de TIMESTAMP WITH TIME ZONE , alors vous êtes fait.

SQLite tient toujours compte des fuseaux horaires et des changements D'heure, il faut faire des piratages pour la contourner. J'ai eu le temps de faire quelques tests et j'ai trouvé plusieurs façons de le faire.

les méthodes suivantes fonctionnent toutes sur ma machine (Heure de Suisse avec des réglages D'économie D'heure, +01:00 resp. +02: 00).

  • julianday('now', 'localtime') - julianday(CREATED_DATE)
  • julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')

voir cas d'espèce:

create table t (CREATED_DATE DATE);

insert into t values (datetime('2015-06-01 00:00:00'));
insert into t values (datetime('2015-12-01 00:00:00'));
insert into t values (datetime('2016-06-01 00:00:00'));
insert into t values (datetime('2016-12-01 00:00:00'));

select datetime('now', 'localtime') as now, 
    created_date, 
    julianday('now') - julianday(CREATED_DATE, 'utc') as wrong_delta_days,
    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday('now') - julianday(CREATED_DATE, 'utc'))||' day', '-1 day')) as wrong_delta,    

    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday('now', 'localtime') - julianday(CREATED_DATE))||' day', '-1 day')) as delta_1, 
    strftime('%j %H:%M:%S',
       datetime('now', 'localtime', 
          '-'||strftime('%Y', CREATED_DATE)||' year', 
          '-'||strftime('%j', CREATED_DATE)||' day', 
          '-'||strftime('%H', CREATED_DATE)||' hour', 
          '-'||strftime('%M', CREATED_DATE)||' minute', 
          '-'||strftime('%S', CREATED_DATE)||' second'
         )) as delta_2,
    strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', '+'||(julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z'))||' day', '-1 day')) as delta_3
from t;


now                 | CREATED_DATE        | wrong_delta_days | wrong_delta  | delta_1      | delta_2      | delta_3
2016-12-08 08:34:08 | 2015-06-01 00:00:00 | 556.398711088113 | 190 09:34:08 | 190 08:34:08 | 190 08:34:08 | 190 08:34:08
2016-12-08 08:34:08 | 2015-12-01 00:00:00 | 373.357044421136 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08
2016-12-08 08:34:08 | 2016-06-01 00:00:00 | 190.398711088113 | 190 09:34:08 | 190 08:34:08 | 190 08:34:08 | 190 08:34:08
2016-12-08 08:34:08 | 2016-12-01 00:00:00 | 7.35704442113638 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08 | 007 08:34:08

j'ai utilisé strftime('%j %H:%M:%S', datetime('0000-01-01T00:00:00', ..., '-1 day')) juste pour le formatage, il n'est pas adapté pour les deltas qui s'étendent sur plus d'un an.

2
répondu Wernfried Domscheit 2016-12-08 19:13:41
la source

autant que j'ai votre problème, il semble que le changement de jour a été perdu pour SQLite, ou pour être exact, que vous devez spécifier ce changement sur la sauvegarde (comme c'est une chaîne et non un vrai date field / transparent-timestamp).

quand vous générez (? si vous le faites) la date, faites-le plein UTC avec fuseau horaire explicite et non local implicite:

SQLite Date / Time

Les Formats

2 à 10 peuvent être éventuellement suivis d'un fuseau horaire indicateur de la forme "[+-]HH:MM" ou juste "Z". La date et l'heure les fonctions utilisent le temps UTC ou "zulu" en interne, et donc le suffixe "Z" est un non-op. Tout suffixe "HH:MM" non nul est soustrait de l'indication date et heure pour calculer l'heure Zoulou. Par exemple, tous les les chaînes de temps suivantes sont équivalentes:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00

Pour être honnêtement, le SQLite avec utc n'est pas mauvais quand il vient à "oublier" le changement de jour, comme UTC ne pas bouger (il est chronométrique heures physiques). Si vous lui avez dit que tout est dans le même fuseau horaire UTC, ça fera juste une simple soustraction et ne donnez pas un f icus à propos de votre jour.

N'est-ce pas aussi julianday('now') de prendre un 'UTC' ? (désolé si je ne la reçois pas, je vais prendre un deuxième coup d'oeil demain)

0
répondu Blag 2016-12-07 05:14:19
la source

je sais que ce n'est pas du tout la façon idéale de faire cela, mais j'ai résolu cela moi-même en utilisant une fonction de compensation dans mon application C# qui tirait les valeurs SQLite. En fait, il m'est venu à l'esprit après avoir écrit ceci que j'aurais pu juste refaire la soustraction de date en C# et sur-écrit mon champ D'âge! Mais comme j'avais juste besoin de modifier les heures (et les jours, si les heures étaient 0 et c'était une date de L'heure avancée) pour la condition donnée, j'ai juste utilisé ceci.

donc j'ai une classe qui fournira un DataTable de résultats basés sur la requête que je fournis à une variable de chaîne statique publique dans cette classe et une autre fonction que j'appelle. Ensuite, j'appelle BindTable() pour lier cette table au ListView dans mon application WPF pour afficher l'information.

je tire mon DataTable en appelant ADOClass.adoDataTable . Une fois que j'ai le DataTable, Je n'ai qu'à itérer les lignes, stocker la Date Créée en tant que variable, et la chaîne D'âge (que j'utilise la fonction pour mettre à jour si nécessaire)) en tant que variable. Si la Date créée répond à la condition .IsDaylightSavingTime() , elle doit obtenir une heure soustraite. Si l'âge était dit, 0 heures (et quelques minutes impaires) vieux, nous devons mettre les jours en arrière un jour, et les heures à 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: je me suis rendu compte plus tard que, parce que j'ai testé cette fonction un jour non-HNR, et que cela était destiné à corriger les problèmes avec les dates D'HNR, que quand il redevient HNR, je crois que les choses vont changer. Je pense que je ferais finir par avoir à ajouter une heure à mes résultats pour les dates non-DST pour le faire fonctionner, à ce point. Il faudrait donc procéder à une vérification supplémentaire si DateTime.Now est conforme à .IsDaylightSavingTime() afin de savoir quoi faire. Je peut re-visiter ce post à l'époque.)

et au cas où quelqu'un serait curieux..... Voici la requête complète que j'exécutais dans 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`

et ceci est ma dernière requête complète pour SQLite à partir de mon 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";

et nouvelle capture d'écran, montrant tout ce qui correspond (sauf le nombre total de jours, que je peux modifier en soustrayant 1/24 de ma fonction C# et mettre à jour de la même manière, pour les dates de L'heure D'été):

enter image description here

mise à JOUR

depuis que Wernfried a trouvé 2 requêtes en SQLite qui annulent la nécessité de cette fonction, je vais accepter que la réponse à comment résoudre ce problème:

Pour Oracle -

  • SELECT (SYSDATE - CREATED_DATE) FROM MyTable

ou à l'aide de to_date syntaxe de format est bon pour la jours.heures, et de faire des conversions. Prendre la portion décimale et multiplier par 24 est bon pour les heures et est indépendant de DST, comme je voulais. Voir ci-dessus pour la requête complète que j'ai utilisé pour le formatage en années, mois, jours, heures et procès-verbaux.

Pour SQLite -

comme Wernfried a trouvé, l'un ou l'autre de ces deux fonctionnera:

julianday('now', 'localtime') - julianday(CREATED_DATE)

julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z')

et cela évite la nécessité de ma fonction ci-dessus.

si vous utilisez:

julianday('now') - julianday(CREATED_DATE, 'utc')

comme j'ai dans le code précédent, ci-dessus, alors vous auriez besoin de mon compensateur DST fonction, loin au-dessus.

0
répondu vapcguy 2016-12-09 00:24:10
la source

Autres questions sur