Stalking the Wild Timezone

David Fetter

Thursday, February 19. 2015

Stalking the Wild Timezone

What time was it?

This is a question that may not always be easy to answer, even with the excellent TIMESTAMPTZ data type. While it stores the UTC timestamp equivalent to the time it sees, it throws away the time zone of the client.

Here's how to capture it. 
CREATE TABLE zonecatcher (

    tstz TIMESTAMPTZ DEFAULT now(),

    tz TEXT DEFAULT current_setting('TimeZone')

);
That's pretty much it for non-hostile INSERTs. You can ensure that INSERTs and UPDATEs capture this information using a trigger function like this:
CREATE OR REPLACE FUNCTION no_fuzzed_inputs()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

    NEW.tstz = now();

    NEW.tz = current_setting('TimeZone');

    RETURN NEW;

END;$$
and a trigger like this:
CREATE TRIGGER no_fuzzed_inputs

    BEFORE INSERT OR UPDATE ON zonecatcher

    FOR EACH ROW

    EXECUTE PROCEDURE no_fuzzed_inputs();
Posted by
David Fetter
at 19:38 | Comments (0) | Trackbacks (0)
Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)
No comments
Add Comment
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

 
   
 

Archives

Subscribe

  • XML

Blog Administration

Open login screen