News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Thursday, September 22, 2016

Support for DateTimeOffset for PostgreSQL provider


We have introduced DateTimeOffset support for PostgreSQL provider in 5.0.11 RC. Here, we want to explain some restrictions of this RDBMS which affect our implementation of that type support.

We warn you that support for date/time with time zone is different from Oracle Database and Microsoft SQL Server implementations. The last two RDBMS has full support for all parts of date/time including time zone, but PostgreSQL doesn't.

If you want to use DateTimeOffset type in domain models on PostgreSQL storage we need to explain some things you may face with.

PostgreSQL has two types which can store both date and time - timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). You may glance at them and think that everything is OK and you can store date/time with time zone in the last one. BUT, if you look carefully you will notice that values of both timestamp and timestamptz types have same size - 8 bytes. Strange, right?

Here is the quote from PostgreSQL documentation:
"All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client."
That means PostgreSQL does not store time zone part of given value at all. It will return you results in the time zone which was specified in PostgreSQL configuration file or the zone which was set manually for certain connection.

For instance, RDBMS instance was configured for time zone  UTC+07. You insert value '2016-09-10 18:19:22.000 +02:00' to some timestamptz column and query it back. The storage will return you '2016-09-10 23:19:22.000 +07:00' and it will do it all the time until you change server time zone.

Such odd implementation of timestamptz influences the results of queries you will receive using DataObjects.Net.

It will influence on date and time parts extraction. You will receive them for a timestamp value in server time zone. It is really important for such parts as Day, Hour, Minute, TimeOfDate and others which may be changed by switching time zone. Also you will get same offset for all DateTimeOffset values.

You also cannot use in LINQ queries methods like

- DateTimeOffset.ToOffset(TimeStamp offset);
- DateTimeOffset.ToLocalTime();
- DateTimeOffset.ToUniversalTime().

The reason is in another PostgreSQL restriction. It provides you a functionality of switching time zone for timestamp values by using  AT TIME ZONE statement or its equivalent - function  timezone(zone, timestamp). This table describes variants of  how it can be used. Note that, if you move a timestamptz value to certain time zone you will get a value without time zone . It makes it impossible to implement support for the methods I mentioned.

However, such type members like DateTimeOffset.LocalDateTime and DateTimeOffset.UtcDateTime will work, because they return values of DateTime type and such operations can be performed by AT TIME ZONE statement and these members are usable in queries.


These restrictions and particularities should be taken into account if you decide to use DateTimeOffset type in a domain model built on PostgreSQL storage. We would recommend you to set time zone in postgresql.config file or with SET statement for connection. It will prevent you from getting values with unexpected offsets. Also you will have to perform switching to time zones you need on client side.

We hope this text will help you avoid mistakes.

15 comments:

  1. I'm wondering how other ORM are solving this..

    ReplyDelete
  2. I'm wondering how other ORM are solving this..

    ReplyDelete

  3. Offersable - Cash Back Offers | Discount Coupons | Bank Offers | Promo Codes.
    Find best Discount Coupons, Cashback Offers, Hot Deals Bank Offers for all major e-commerce stores in the India



    Amazon cashback offers 2018Cashback Deals with sbi,hdfc,icici,axis banks,credit&debit card,
    Amazon Cashback Offers 2018 Upto 65% Cashback Deals, AMAZON CASHBACK OFFERS 2018, DISCOUNT COUPON OFFERS 2018, Amazon cashback offers with sbi hdfc icici axis banks credit&debit card offers more. amazon cashback offers2018,amazon india cashback,amazon offers on mobiles,amazon cashback2018,amazon offers 2018



    cashbackoffer amazon coupon code| amazon coupon promo code| amazon offer coupon code|
    amazon coupon code| amazon coupon promo code| amazon offer coupon code


    About Airport Parking,Accor HotelsAffordable Asia,Agoda,Air Asia Go,Akbar Travels,Amoma,Azores Getaways,Big Bus Tours,Booking,CarmelLimo,CatchThatBus,CheapAir,CheapTickets Singapore,Cleartrip,Ctrip English speaking,Etihad Airways,Expedia,Expedia India, Expedia Indonesia Hotels, Flights & Packages,Travel Coupons, Offers: Flights, Hotels, Bus Promo Codes

    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes


    flipkart cashback offer,
    CashBack Offers, Discount Coupons


    Pepperfry, Mobikwik, Firstcry, Fab Hotels, Ebay, Aliexpress, Adidas, Healthkart, Pepperfry, Shoppers Stop, FlipKart, Amazon, Lurap, Giftalove, Reebok, SBI Card, CoolWinks, Housejoy, Shine, Jaypore, netmeds, FabAlley, Prettysecrets, FlowerAura, Clovia, Shopclues, Printvenue, Firstcry, Travelguru, Myntra, Zivame, Jabong, Ferns N Petals,Healthkart, Pepperfry, Shoppers Stop, FlipKart, Amazon


    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes


    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes

    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes

    ReplyDelete
  4. nice post.super article .iam follow your website.


    Flipkart CashBack Offers-SBI, HDFC, ICICI, Citibank Cards&Flipkart HDFC Cards Offers"
    flipkart bank offers,flipkart cash back coupons,flipkart offers 2018,flipkart coupons 2018,flipkart cashback offers 2018,flipkart cashback offers,flipkart cash back offers,flipkart cashback coupons,flipkart cashback,Flipkart Cashback Offers,Flipkart Bank Offers,Flipkart Deals 2018, flipkart upcoming offers on mobiles, hdfc credit card offers on flipkart 2018 flipkart sbi offer big billion day, flipkart hdfc offer, flipkart hdfc offer august 2018 sbi flipkart offer 2018 flipkart icici offer


    FLIPKART CITIBANK OFFER,FLIPKART ICICI OFFER,
    SBI CARD OFFERS ON FLIPKART,
    FLIPKART CASHBACK OFFER TODAY,FLIPKART CREDIT CARD OFFERS,
    ALL BANK CREDIT CARD OFFERS,HDFC CREDIT CARD OFFERS ON FLIPKART,
    AXIS BANK CREDIT CARD OFFERS ON FLIPKART,
    CITIBANK CREDIT CARD OFFERS ON FLIPKART,SBI FLIPKART OFFER



    Flipkart Cashback Offers||Flipkart Bank Offers||Flipkart Deals 2018||flipkart upcoming offers,"
    "Flipkart Cashback Offers || Flipkart Bank Offers || Flipkart Deals 2018 ||" "flipkart upcoming offers on mobiles, hdfc credit card offers on flipkart 2018, sbi credit card offers on flipkart today, flipkart hdfc offer terms and conditions, sbi flipkart offer 2018, flipkart cashback phonepe, flipkart hdfc offer august 2018, flipkart icici offer




    "cashback,vouchers,coupons,discounts,offers,deals,promo codes,&Cashback Offers"
    "cash back coupons "Coupons, Discounts & Offers on 1500+ Shopping Sites in India. Get Extra Cashback Everytime You Shop Online using Coupons of Amazon, Flipkart, Tata CLiQ, Nykaa etc."cashback, vouchers, coupons, discounts, offers, deals, promo codes,Coupons, Promo Codes & Cashback Offers

    flipkart is Offering Cashback Offers Flipkart hdfc cashback offers You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart SBI Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart AXIS Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart YES Cashback Offer You will get upto 75% Discount

    Flipkart is one of the biggest world wide eCommerce Site so here you will getFlipkart big billion day offers

    Flipkart is one of the biggest world wide eCommerce Site so here you will getflipkart bank cashback offersso

    ReplyDelete
  5. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.

    flipkart is Offering Cashback Offers Flipkart hdfc cashback offers You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart SBI Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart AXIS Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart YES Cashback Offer You will get upto 75% Discount

    Flipkart is one of the biggest world wide eCommerce Site so here you will getFlipkart big billion day offers

    Flipkart is one of the biggest world wide eCommerce Site so here you will getflipkart bank cashback offersso


    Flipkart cashback offers on mobiles,
    Flipkart cashback offers,
    Flipkart cashback coupons,
    Flipkart upcoming offers on mobiles,
    Flipkart HDFC cashback offers,
    Flipkart SBI cashback offers,
    Flipkart AXIS cashback offers,
    Flipkart Citibank cashback offers,
    Flipkart cashback on mobiles,
    Flipkart cashback offers today,
    Flipkart big billion day cashback offers,
    Flipkart big billion days cashback offers,
    big billion day cashback offers,
    Flipkart big billion days offers,
    flipkart cashback on mobiles,
    flipkart citibank offer,
    sbi credit card offers on flipkart today,
    flipkart upcoming offers on mobiles,
    hdfc credit card offers on flipkart,
    flipkart upcoming big billion day,
    flipkart upcoming sale,
    sbi credit card offers on mobiles,
    flipkart offers today,
    flipkart offers on mobiles,
    flipkart offers today mobile phones,
    flipkart cashback offers,
    flipkart cashback coupons,
    flipkart sale today offer,
    flipkart cashback offers today,
    cashback offers,
    flipkart cashback offer today

    ReplyDelete

  6. Flipkart SBI Offer 2019: 10% Instant Cashback.
    Flipkart HDFC Offer 2019: 10% Instant Offer.
    Flipkart Cashback offer 2019: 10% Cashback.
    Flipkart SBI Offer 2019: 10% Instant Cashback.
    Flipkart HDFC Offer 2019: 10% Instant Offer.
    Flipkart Cashback offer 2019: 10% Cashback
    SBI Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    HDFC Credit Ca rd /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    Citi Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    Axis Bank Credit Card /Debit Card Offers 10% Instant Cashback (Extra 5% on Axis Buzz Cards)
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    ICICI Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    YES Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    IDBI Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    Kotak Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    flipkart sbi debit card offers,flipkart offers


    flipkart sbi offers
    flipkart hdfc offers
    flipkart axis offers
    flipkart sbi offer
    flipkart sbi debit card emi
    flipkart sbi credit card offer
    flipkart sbi debit card emi
    flipkart sbi credit card offers
    flipkart hdfc,
    flipkart hdfc offer,
    flipkart hdfc credit card offer,
    flipkart icici debit card emi
    flipkart icici credit card offer
    flipkart icici offer
    flipkart axis,
    flipkart axis bank offer

    ReplyDelete

  7. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.


    flipkart Credit card offers,flipkart offers,flipkart offer
    flipkart emi,flipkart debit card emi,flipkart emi on debit card,,flipkart with emi,flipkart emi debit card sbi,flipkart emi sbi debit card,
    flipkart sbi,flipkart sbi debit card emi,flipkart sbi offer


    flipkart hdfc,flipkart hdfc offer,flipkart hdfc credit card offer,flipkart hdfc bank offer,flipkart icici debit card emi,flipkart icici credit card offer,flipkart icici credit card offers,flipkart offer for icici credit card,flipkart icici offer,flipkart icici debit card offer,flipkart debit,flipkart debit card emi,flipkart emi for debit card,flipkart sbi debit card emi,flipkart debit emi,flipkart icici debit card emi,flipkart debit card offers,flipkart offer debit cardflipkart debit card emi eligibility smsflipkart debit card emi icici,flipkart sbi debit card offer,flipkart hdfc debit card emi,flipkart credit,flipkart credit card offers,flipkart offer credit card,flipkart offer for credit card,flipkart offers with credit cards,flipkart hdfc credit card offer,flipkart credit card,flipkart offer,flipkart sale,

    Flipkart SBI Offer 2019: 10% Instant Cashback.
    Flipkart HDFC Offer 2019: 10% Instant Offer.
    Flipkart Cashback offer 2019: 10% Cashback.
    Flipkart SBI Offer 2019: 10% Instant Cashback.
    Flipkart HDFC Offer 2019: 10% Instant Offer.
    Flipkart Cashback offer 2019: 10% Cashback.
    SBI Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    HDFC Credit Ca rd /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    Citi Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashbackhttps://www.flipkart.com/?affid=crishkuma1
    Axis Bank Credit Card /Debit Card Offers 10% Instant Cashback (Extra 5% on Axis Buzz Cards)
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    ICICI Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    YES Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    IDBI Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    Kotak Bank Credit Card /Debit Card Offers 10% Instant Cashback
    Get Cashback
    https://www.flipkart.com/?affid=crishkuma1
    flipkart sbi debit card offers,flipkart offers


    flipkart offers


    flipkart axis offers

    ReplyDelete



  8. flipkart debit card offers,
    flipkart debit card emi,
    debit card offers in flipkart,
    flipkart sbi debit card offers,
    sbi debit card offers on flipkart,,
    flipkart debit card emi axis,,
    flipkart debit card emi sms number
    flipkart offers for hdfc debit card,
    hdfc debit card offers on flipkart
    flipkart icici debit card emi offer,,
    flipkart debit card emi message,,
    kotak debit card offers on flipkart
    flipkart icici debit card offer,,,
    icici debit card offers on flipkart
    flipkart debit card emi kotak,
    flipkart sbi debit card emi offer,
    flipkart hdfc debit card emi offer,
    flipkart debit card emi sbi sms,,
    flipkart debit card emi hdfc eligibility
    flipkart axis bank debit card offers
    flipkart debit card shopping
    flipkart debit card loan,
    flipkart debit card no cost emi,
    flipkart debit card option,
    flipkart debit card purchase,
    flipkart debit card offers on mobiles,
    flipkart debit card mobile
    flipkart kotak debit card offer,
    flipkart debit card scheme,,
    flipkart debit card payment offers,
    idbi debit card offers on flipkart,,
    sbi debit card offers in flipkart,,
    flipkart debit card installment,
    flipkart sbi debit card offer today,,
    flipkart bank card offers,,
    flipkart debit card discount
    flipkart axis debit card offer,
    yes bank debit card offers on flipkart,
    flipkart offers for sbi debit card
    flipkart big billion day debit card offers,
    flipkart debit card ,
    flipkart debit card emi offer details,
    flipkart offers for icici debit card,,
    flipkart pnb debit card offer,
    flipkart sbi debit card offer terms and conditions,
    hdfc debit card offers in flipkart,
    axis bank debit card offers in flipkart

    ReplyDelete
  9. Thank you very much for this nice and informative post.To be honest I was planning to write about this topic and I intend to post a link to this wonderful article once I have it done.I learned myself few other things I never thought about.

    visit
    click here
    website
    blog

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete