SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PostgreSQL: Simple compare of date/times not wokring.

    I'm new to PostgreSQL. Using version 8.4.1.

    Code:
    CREATE TABLE user_persistent_authentication
    (
      token character(128) NOT NULL,
      user_id bigint NOT NULL,
      date time with time zone NOT NULL DEFAULT now(),
      active boolean NOT NULL DEFAULT true,
      CONSTRAINT primary_key_user_persistent_authentication PRIMARY KEY (token),
    )
    Why doesn't this query work?

    Code:
    SELECT date FROM user_persistent_authentication WHERE date < now();
    Code:
    ERROR:  operator does not exist: time with time zone > timestamp with time zone
    Is there another way I have to compare date/times?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    instead of TIME WITH TIME ZONE, maybe you want TIMESTAMP WITH TIME ZONE

    note that the now() function returns TIMESTAMP WITH TIME ZONE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I understand. You can't compare the two different datetime types.

    Is there a PostgreSQL function that returns the current datetime as a TIME WITH TIME ZONE so it can be compared to my stored 'date' TIME WITH TIME ZONE?

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seams this is what CURRENT_TIME is used for.
    Thank you for your help.


Tags for this Thread

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
  •