Ignorer complètement les fuseaux horaires dans Rails et PostgreSQL

Question

Je fais face à des dates et des heures dans les Rails et Postgres et en cours d'exécution dans ce numéro:

La base de données est en UTC.

L'utilisateur définit une fois la zone de choix dans l'application Rails, mais c'est seulement pour être utilisé lors de l'obtention d'utilisateurs, heure locale, pour comparer les temps.

L'utilisateur stocke un temps, disons le 17 Mars 2012, à 7 heures du soir. Je ne veux pas fuseau horaire conversions ou le fuseau horaire pour être stocké. Je veux juste que la date et l'heure enregistrées. De cette façon, si l'utilisateur a changé de fuseau horaire, Il serait toujours montrer le 17 Mars 2012, à 7 heures du soir.

Je n'utilise que les utilisateurs fuseau horaire spécifié pour obtenir les dossiers "avant" ou "après", à l'heure actuelle dans le fuseau horaire local.

Je suis actuellement en utilisant 'timestamp sans time zone' mais quand j'ai récupérer les enregistrements, les rails (?) convertit le fuseau horaire dans l'application, que je ne veux pas.

Appointment.first.time
 => Fri, 02 Mar 2012 19:00:00 UTC +00:00 

Parce que les enregistrements dans la base de données semblent sortir que l'UTC, mon hack est de prendre le temps écoulé, retirez le fuseau horaire avec la Date).strptime(str, "%m/%d/%Y") " et ensuite faire ma requête:

.where("time >= ?", date_start)

Il semble qu'il doit y avoir un moyen plus facile de les ignorer fuseaux horaires. Des idées?

Answer 1

Le type de données timestamp dans PostgreSQL est - timestamp without time zone par défaut.
L'autre option est d' timestamptz (timestamp with time zone).

Époque

En interne, ces données sont stockées sous forme de nombre à partir d'une époque. Postgres utilise l'époque du premier moment de la première journée de l'an 2000 à l'UTC, qui est, 2000-01-01T00:00:00Z. Huit octets sont utilisés pour stocker le nombre. Selon une compilation option, ce nombre est soit:

• Un nombre entier (par défaut), avec 0 à 6 chiffres d'une fraction de seconde
• Un nombre à virgule flottante (obsolète), avec 0 à 10 chiffres d'une fraction de seconde, où la précision se dégrade rapidement pour des valeurs plus loin de l'époque.

Notez que Postgres ne pas utiliser le temps Unix. Postgres' époque est le premier moment de 2000-01-01 plutôt que d'Unix' 1970-01-01. Alors que le temps Unix a une résolution de secondes, Postgres garde des fractions de secondes.

timestamp

Si vous définissez un type de données timestamp [without time zone] vous dites Postgres: "je ne suis pas fournir un fuseau horaire explicitement, supposons que le fuseau horaire actuel à la place. Postgres enregistre le timestamp qu'est - ignorer un fuseau horaire modificateur si vous devez en ajouter un!

Plus tard lorsque vous afficher qu' timestamp, vous obtenez ce que vous avez entré littéralement. Avec le même paramètre de fuseau horaire, tous si bien. Si le paramètre de fuseau horaire pour la session de changements, de sorte que le sens de l' timestamp - la valeur reste la même.

timestamptz

La manipulation pour timestamp with time zone est subtilement différent. Je cite le manuel ici:

Pour timestamp with time zone, la valeur stockée en interne est toujours en UTC (Temps Universel Coordonné ...)

En gras c'est moi qui souligne. Le temps de la zone elle-même est jamais stockées. Il est utilisé pour calculer la fonction horodatage UTC, qui est stocké. Si vous n'avez pas ajouter un fuseau horaire modificateur pour un timestamptz sur l'entrée de l'heure locale de la zone de la session est supposé. Tous les calculs sont fait avec UTC valeurs d'horodatage. Si vous avez (ou peut-être) traiter avec plus de fuseau horaire, vous devez utiliser timestamptz.

Les Clients aiment psql ou pgAdmin (ou Ruby) sont présentés avec l'UTC valeur d'horodatage selon le fuseau horaire actuel (timestamp ou timestamp with time zone ), ou selon une demande de fuseau horaire (voir ci-dessous). C'est toujours la même point dans le temps, seul le format d'affichage varie. Ou, comment le manuel met:

Tous fuseau horaire-courant des dates et les heures sont stockées en interne en UTC. Ils sont convertis à l'heure locale dans la zone spécifiée par le fuseau horaire paramètre de configuration avant d'être affichés pour le client.

Considérons cet exemple simple (psql):

db=# SELECT '2012-03-05 20:00+03'::horodatage avec le temps de la zone;
timestamptz
------------------------
 2012-03-05 18:00:00+01

En gras c'est moi qui souligne. Ce qui s'est passé ici?
J'ai choisi arbitrairement de décalage de fuseau horaire +3. Pour Postgres, ce est juste un des nombreux moyens d'entrée de l'horodatage UTC 2012-03-05 17:00:00. Le résultat de la requête est affichée dans le format de l'heure courante de la zone: 2012-03-05 18:00:00+01. (Mon serveur à Vienne/Autriche est configuré avec la fonction de fuseau horaire +1.)

Postgres a déjà oublié comment cette valeur a été entrée. Tous il se souvient c'est la valeur et le type de données. Tout comme avec un nombre décimal. '03.4'::numeric, '3.40'::numeric ou '3.4'::numeric - tous les résultats dans le exact même valeur interne.

Dès que vous obtenir une prise sur cette logique, vous pouvez faire ce que vous voulez. Tout ce qui manque maintenant, c'est un outil pour interpréter ou de les représenter timestamp littéraux selon un fuseau horaire spécifique. C'est là que l' AT TIME ZONE construire. Être conscient de deux différents cas d'utilisation. timestamptz est converti timestamp , et vice-versa.

Pour entrer dans l'horodatage UTC 2012-03-05 17:00:00:

SELECT '2012-03-05 17:00:00'::timestamp AT TIME ZONE 'UTC'

Pour afficher la même valeur d'horodatage UTC timestamp:

SELECT '2012-03-05 17:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'

C'est vrai, AT TIME ZONE 'UTC' deux fois. Le premier interprète le timestamp littérale que l'UTC l'horodatage et des sorties du type timestamptz. Le second convertit retour à l' timestamp [without time zone]. Ou, moins à confusion:

SÉLECTIONNEZ ts TIME ZONE 'UTC'
À PARTIR DE (VALEURS
 ('2012-03-05 17:00:00+0')
 ,('2012-03-05 18:00:00+1'::timestamp avec fuseau horaire)
 ,('2012-03-05 18:00:00+1'::timestamp) --1chargé footgun!
 ,('2012-03-05 11:00:00'::timestamp AU MOMENT de ZONE '+6') 
 ,('2012-03-05 17:00:00'::timestamp AU MOMENT de la ZONE 'UTC') 
 ,('2012-03-05 10:00:00'::timestamp AU MOMENT ZONE "Mexique/BajaSur') --2
 ,( TIMESTAMP '2012-03-05 07:00:00', AU TEMPS DE LA ZONE DE TVH') --2
 ) V(ts);

Retourne 7 (6) identiques lignes de la même horodatage UTC 2012-03-05 17:00:00.

¹ La troisième ligne, a marqué en tant que chargé footgun fonctionne pour moi, mais seulement par hasard. Si vous explicitement convertir un timestamp littéral timestamp [without time zone], le décalage de fuseau horaire seront ignorés! Il est interprété en fonction de la locale de fuseau horaire à la place, qui se trouve être le même fuseau horaire +1 dans mon cas. Mais probablement pas pour vous - qui se traduira par une valeur différente.

² Noter que les deux dernières lignes avec le temps le nom de la zone et à la fois zone d'abréviation pour Hawaii sont soumis à l'heure d'été et peuvent différer.
_{L'Heure d'été n'est pas parmi les plus brillants des idées de l'humanité jamais venu avec.}

Parce que votre base de données est en UTC, AT TIME ZONE 'UTC' est la valeur par défaut et peuvent être supprimés localement.

Quant à vos questions:

L'utilisateur stocke un temps, disons le 17 Mars 2012, à 7 heures du soir. Je ne veux pas fuseau horaire les conversions ou le fuseau horaire pour être stocké.

Fuseau horaire lui-même n'est jamais stockée. Utilisez l'une des méthodes ci-dessus pour entrer un horodatage UTC.

Et:

Je n'utilise que les utilisateurs fuseau horaire spécifié pour obtenir les dossiers "avant" ou 'après' l'heure actuelle dans le fuseau horaire local.

Vous pouvez utiliser une requête pour tous les clients dans des fuseaux horaires différents.
Pour mondiale absolue, temps:

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

Pour le temps en fonction de l'horloge locale:

SELECT * FROM tbl WHERE time_col > now()::time

Pas fatigué d'informations de fond sur la date/l'heure de la manipulation, encore? Il n'est plus dans le manuel.