[yunqa.de] Re: Question about UTC and local time
- From: "Edwin Yip" <mindvisualizer@xxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sat, 5 Jul 2008 21:02:25 +0800
Thank Ralf and Jerry,
To conclude, If I don't need to care about different time zones, e.g. I'm
not using a database which will be updated by users all around the world,
but just use a database in a desktop program, can I treat all datetime
values to be local time by doing the following?
1. Before saving TDateTime values to the database convert them using
DateTimeToJulianday supplied by DISQLITE pacakge.
2. After retrieving Julian day values from the database convert them
using JuliandayToDateTime supplied by DISQLITE pacakge.
3. Use the 'now' and 'localtime' modifiers together in SQL.
I don't do any +8 or -8 things, Will there be any issue? Especially point 3.
Thanks again.
Best Regards,
Edwin Yip
On Sat, Jul 5, 2008 at 8:11 PM, Jerry Hayes <jhayes@xxxxxxxxxxxxxxxxx>
wrote:
> If I have a value in Delphi which is of type TDateTime and equals to
> Delphi function Now(). How to store this value to the DB correcty?
>
> Edwin, (not Ralf here, but I'll try to help ;)
>
> I think you have 2 real options, since as it's been stated, there's no
> native date type.
>
> 1. Store everything as a float, with the dates calculated to UTC before
> storage. Since you're at +8, all the stored values would be 8 hours off for
> your time zone, you'd just have to have the local application realize that
> they were. What you lose here is what the original time zone was, (unless
> you store it in an additional field and have all your dates as two fields).
>
> 2. Store everything as text, with dates calculated to UTC before storage
> and then add the time zone as text. YYYY-MM-DD HH:NN:SS.SSS **always** as
> UTC and then add your zone information at the end +08, etc. So you get
> YYYY-MM-DD HH:NN:SS.SSS ZZZ (where 'Z' is the time zone). One field, just
> more storage bytes.
>
> Either of these two give you a fixed point in time that can be accurately
> indexed and compared – the first since it's numeric, the second because the
> date format is formatted so it can be compared. The key point in both
> options is that you're converting to UTC **before** storage.
>
> HTH,
>
> Jerry
>
> No virus found in this outgoing message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.4.5/1533 - Release Date: 7/3/2008
> 7:19 PM
>
Other related posts:
