Professional Web Applications Themes

int4 -> unix timestamp -> sql timestamp; abstime? - PostgreSQL / PGSQL

Hello, what is the opposite of cast(extract('epoch' from now()) as int)? The only thing I found that works is cast(cast(... as abstime) as timestamp) and the doentation says abstime shouldn't be used, and may disappear. What should I use instead? -- FreeBSD 4.10-STABLE 12:01AM up 15:39, 7 users, load averages: 0.08, 0.04, 0.01 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]...

  1. #1

    Default int4 -> unix timestamp -> sql timestamp; abstime?

    Hello,

    what is the opposite of cast(extract('epoch' from now()) as int)?
    The only thing I found that works is
    cast(cast(... as abstime) as timestamp)
    and the doentation says abstime shouldn't be used, and may
    disappear. What should I use instead?

    --
    FreeBSD 4.10-STABLE
    12:01AM up 15:39, 7 users, load averages: 0.08, 0.04, 0.01

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Roman Neuhauser Guest

  2. #2

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    Roman Neuhauser <neuhauserchello.cz> writes:
    > what is the opposite of cast(extract('epoch' from now()) as int)?
    > The only thing I found that works is
    > cast(cast(... as abstime) as timestamp)
    > and the doentation says abstime shouldn't be used, and may
    > disappear. What should I use instead?
    The recommended locution is

    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

    Of course you can wrap this up in a function if you prefer (not sure why
    we haven't done so already).

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Tom Lane Guest

  3. #3

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?


    On Jan 12, 2005, at 8:35, Tom Lane wrote:
    > The recommended locution is
    >
    > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    >
    > Of course you can wrap this up in a function if you prefer (not sure
    > why
    > we haven't done so already).
    It's in the queue for 8.1.

    <http://candle.pha.pa.us/mhonarc/patches2/msg00090.html>

    Michael Glaesemann
    grzm myrealbox com


    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Glaesemann Guest

  4. #4

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    # [email]tglsss.pgh.pa.us[/email] / 2005-01-11 18:35:18 -0500:
    > Roman Neuhauser <neuhauserchello.cz> writes:
    > > what is the opposite of cast(extract('epoch' from now()) as int)?
    > > The only thing I found that works is
    > > cast(cast(... as abstime) as timestamp)
    > > and the doentation says abstime shouldn't be used, and may
    > > disappear. What should I use instead?
    >
    > The recommended locution is
    >
    > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    Have I missed this in the manual?
    > Of course you can wrap this up in a function if you prefer (not sure why
    > we haven't done so already).
    I most surely will, and I'm glad to hear this will be included in 8.1.

    Thanks for the replies!

    --
    FreeBSD 4.10-STABLE
    1:25AM up 17:04, 9 users, load averages: 0.11, 0.06, 0.01

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Roman Neuhauser Guest

  5. #5

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    Roman Neuhauser <neuhauserchello.cz> writes:
    >> The recommended locution is
    >>
    >> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    > Have I missed this in the manual?
    The 8.0 docs mention this in the discussion of extract(epoch), but I'm
    not sure if it was there before. It's been discussed in the mailing
    list archives many times...

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Tom Lane Guest

  6. #6

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    On Tue, Jan 11, 2005 at 06:35:18PM -0500, Tom Lane wrote:
    > Roman Neuhauser <neuhauserchello.cz> writes:
    > > what is the opposite of cast(extract('epoch' from now()) as int)?
    > > The only thing I found that works is
    > > cast(cast(... as abstime) as timestamp)
    > > and the doentation says abstime shouldn't be used, and may
    > > disappear. What should I use instead?
    >
    > The recommended locution is
    >
    > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    I think this should read:

    SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
    /* ^^^^^^^^^^^^^^^^^^ */

    to conform with the *n*x standard.

    Cheers,
    D
    --
    David Fetter [email]davidfetter.org[/email] [url]http://fetter.org/[/url]
    phone: +1 510 893 6100 mobile: +1 415 235 3778

    Remember to vote!

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    David Fetter Guest

  7. #7

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    David Fetter <davidfetter.org> writes:
    >> The recommended locution is
    >>
    >> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    > I think this should read:
    > SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
    > /* ^^^^^^^^^^^^^^^^^^ */
    It's correct as written; your modification throws it off by the local GMT
    offset.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Tom Lane Guest

  8. #8

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    # [email]tglsss.pgh.pa.us[/email] / 2005-01-11 19:31:19 -0500:
    > Roman Neuhauser <neuhauserchello.cz> writes:
    > >> The recommended locution is
    > >>
    > >> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    >
    > > Have I missed this in the manual?
    >
    > The 8.0 docs mention this in the discussion of extract(epoch), but I'm
    > not sure if it was there before. It's been discussed in the mailing
    > list archives many times...
    My searches on google.com and of the postresql.org doentation sets
    haven't turned anything up. Any chance of this getting into the 7.4
    docs?

    --
    FreeBSD 4.10-STABLE
    1:53AM up 17:31, 9 users, load averages: 0.05, 0.03, 0.00

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Roman Neuhauser Guest

  9. #9

    Default Re: int4 -> unix timestamp -> sql timestamp; abstime?

    On Tue, Jan 11, 2005 at 07:44:46PM -0500, Tom Lane wrote:
    > David Fetter <davidfetter.org> writes:
    > >> The recommended locution is
    > >>
    > >> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';
    >
    > > I think this should read:
    >
    > > SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
    > > /* ^^^^^^^^^^^^^^^^^^ */
    >
    > It's correct as written; your modification throws it off by the
    > local GMT offset.
    Thanks for the heads-up. I was just about to Do The Wrong Thing(TM)
    in a doc patch re: ALTER COLUMN TYPE :)

    Cheers,
    D
    --
    David Fetter [email]davidfetter.org[/email] [url]http://fetter.org/[/url]
    phone: +1 510 893 6100 mobile: +1 415 235 3778

    Remember to vote!

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    David Fetter Guest

Similar Threads

  1. Unix timestamp values
    By PlastechFish in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 10th, 08:57 PM
  2. Date comparisons using UNIX timestamp?
    By sue in forum PHP Development
    Replies: 4
    Last Post: February 17th, 10:45 PM
  3. Concerting a Date Object into a UNIX Timestamp
    By Dennis Oelkers in forum Ruby
    Replies: 2
    Last Post: November 19th, 04:30 PM
  4. convert UNIX timestamp
    By Jason Normandin in forum Perl / CGI
    Replies: 1
    Last Post: October 4th, 03:07 PM
  5. [PHP] convert mysql datetime to unix timestamp
    By Adam Voigt in forum PHP Programming
    Replies: 1
    Last Post: July 28th, 04:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139