Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    18

    Unanswered: Convert Local Time To UTC

    Hi All

    I have a table that contains 521 cities with lat long coordinates in a PostgreSQL database

    Does anyone know of a way to get the UTC number for each of the cities

    So for example lets say I live in Germany. It would simply return + 1

    Or if i supplied it with a time stamp from the UK, it would convert it to UTC time i.e. 2pm would become 3pm

    The offset for the hours will be different for different time zones

    The database contains a table where records are inserted every couple of seconds. I want to convert the time stamp to UTC for analysis for a Report I'm building. The table that contains the 521 records is a reference table

    Is this possible in PostgreSQL SQL or will i have to go down the Python route

    Thank you for your time

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The conversion from local to UTC time is more complex than most people expect it to be... Not only do you need to consider location and time, but date too.

    Many areas observe daylight savings time, but when they start and stop observing it varies over time. Few (if any) are consistent about observing leap seconds. There are a number of locations that also have arbitrary adjustments to their time.

    This conversion can be in almost any language, and it is best handled in a data-driven fashion. I don't know of any Python or PostgreSQL specific routines that handle it well, much less completely correctly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    I don't know of any Python or PostgreSQL specific routines that handle it well, much less completely correctly
    Hmm, what about:
    Code:
    select current_timestamp, current_timestamp at time zone 'UTC'
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Shammat's example works great for the special case of the current moment in time, assuming that the OS that is running PostgreSQL handles the computation correctly (most, but not all do it correctly).

    This isn't a general case and any conversion derived from it will only be correct for the current location until the next "time change" occurs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2013
    Posts
    18
    Hi Pat/shammat

    Thanks for the quick response
    I was able to do this using Python (eventually after some hair pulling and forum searching )

    There was a Python script written in a blog below

    https://gist.github.com/pamelafox/2288222
    Converting Addresses to Timezones in Python

    I was able to get the script to work and get the time zones for each of my latitude and longitude points and then manually find the offsets from the world clock

    Since the data i am looking at is static i didn't have to worry about day light savings

    I was able to create a table with the offsets import in my new time zone table and then link it back to the original data. Once that was done it was a case of updating the existing UTC column with the code

    SET utc_timestamp = AL1.local_timestamp- AL2.utc_offset * interval '1 hour'
    Thanks again for your time
    Last edited by maccten; 04-01-13 at 10:50. Reason: didnt see shammats answer

  6. #6
    Join Date
    Feb 2013
    Posts
    18
    Sorry to bring this up again

    I appear to have made a massive error in judgement

    I have a table full of timestamps
    These timestamps are in the local timezone of the country (This hasn't been specified in the field)

    Unfortunately i have just realized that these timestamps range across the daylight savings time barrier which means my previous attempts are basically redundant

    My table also holds the timezone of each country which i have managed to pull from the world clock website

    Does anyone know, how i can assign the timezone to each local time stamp for each country which then i can use to convert to UTC?

    Thank you for your time

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As I said before, this is a non-trivial problem!

    Can you determine on a timestamp-by-timestamp basis which time zone is applicable? If you can't, there is no way to solve this problem (I guarantee that).

    For most time zones, there is an offset in minutes from UTC. For a few time zones, that offset has to be in seconds due to the decision to process "leap seconds" when DST offset begins/ends. I don't know of any time zones that need sub-second accuracy.

    The easiest way to handle the offset processing is to store a table that contains: Time zone, UTC offset (minutes or seconds, depending on your need for precision), and the begin and end date/time combination. Since the date for the DST changes varies from year to year, you'll need to have a row for each offset that applies to a given time zone.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •