Thread: Splitting Timestamps

I have several columns in my database that are timestamps.  My developers are asking me how to split the timestamp so that they can look at either the date or at the time portion.

I know I can do a select to_date(now(),'yyyy-mm-dd') and it will return the date.  However, how do I get the time?  Also, is this the proper way to get the date portion of a timestamp?

Thanks,

Chris

Chris Hoover wrote:
> I have several columns in my database that are timestamps.  My developers
> are asking me how to split the timestamp so that they can look at either the
> date or at the time portion.
>
> I know I can do a select to_date(now(),'yyyy-mm-dd') and it will return the
> date.  However, how do I get the time?  Also, is this the proper way to get
> the date portion of a timestamp?

select now()::timetz;
select now()::time;
select now()::date;

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

am  25.07.2006, um 12:54:35 -0400 mailte Chris Hoover folgendes:
> I have several columns in my database that are timestamps.  My developers
> are asking me how to split the timestamp so that they can look at either
> the
> date or at the time portion.

The CAST-Operater is your friend:

est=# select now();
              now
-------------------------------
 2006-07-25 19:12:36.744262+02
(1 row)

test=# select now()::time;
       now
-----------------
 19:12:41.803128
(1 row)

test=# select now()::date;
    now
------------
 2006-07-25
(1 row)

test=#


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On Tue, 2006-07-25 at 12:54 -0400, Chris Hoover wrote:
> I know I can do a select to_date(now(),'yyyy-mm-dd') and it will
> return the date.  However, how do I get the time?

Casting is the better option, but the to_date format spec handles a lot
more than just dates. See:

http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html


The casting way:
        rkh@csb-dev=> select '2006-07-25 21:24'::time;
           time
        ----------
         21:24:00

        rkh@csb-dev=> select '2006-07-25 21:24'::date;
            date
        ------------
         2006-07-25

The to_char way:
        rkh@csb-dev=> select to_char(now(),'HH24:MI');
         to_char
        ---------
         10:44

Or the baroque way for your, ahem, timeless applications:
        rkh@csb-dev=> select to_char('2006-07-25 20:24'::timestamp,'MI
        "minutes" past the HH24th hour');
                    to_char
        -------------------------------
         24 minutes past the 20th hour

        rkh@csb-dev=> select to_char('2006-07-25 21:24'::timestamp,'MI
        "minutes" past the HH24th hour');
                    to_char
        -------------------------------
         24 minutes past the 21st hour

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

revoohc@gmail.com ("Chris Hoover") writes:
> I have several columns in my database that are timestamps.  My
> developers are asking me how to split the timestamp so that they can
> look at either the date or at the time portion.  I know I can do a
> select to_date(now(),'yyyy-mm-dd') and it will return the date. 
> However, how do I get the time?  Also, is this the proper way to get
> the date portion of a timestamp?  Thanks, Chris

Look at the function date_part(time_type, timestamp)
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/lsf.html
Do not worry  about the bullet that  has got your name on  it. It will
hit you and it will kill  you, no questions asked. The rounds to worry
about are the ones marked: TO WHOM IT MAY CONCERN.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Alvaro Herrera wrote:
> Chris Hoover wrote:
>> I have several columns in my database that are timestamps.  My developers
>> are asking me how to split the timestamp so that they can look at either the
>> date or at the time portion.
>>
>> I know I can do a select to_date(now(),'yyyy-mm-dd') and it will return the
>> date.  However, how do I get the time?  Also, is this the proper way to get
>> the date portion of a timestamp?
>
> select now()::timetz;
> select now()::time;
> select now()::date;

What's the inverse?  Say I have a DATE and a TIME, and want to
create a TIMESTAMP with them?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEzGPIS9HxQb37XmcRAgWyAKCE/xGJrieisqqHcwxFGcreQfFG8wCggL8n
wiDGRD0xekMKVb01R1uPM4I=
=hE1R
-----END PGP SIGNATURE-----

Ron Johnson <ron.l.johnson@cox.net> schrieb:
> >> I know I can do a select to_date(now(),'yyyy-mm-dd') and it will return the
> >> date.  However, how do I get the time?  Also, is this the proper way to get
> >> the date portion of a timestamp?
> >
> > select now()::timetz;
> > select now()::time;
> > select now()::date;
>
> What's the inverse?  Say I have a DATE and a TIME, and want to
> create a TIMESTAMP with them?

You can CAST it:

test=# select '2006/07/29 10:00:00'::timestamp;
      timestamp
---------------------
 2006-07-29 10:00:00
(1 row)

or:

test=# select ('2006/07/29'::date || ' ' || '10:00:00'::time)::timestamp;
      timestamp
---------------------
 2006-07-29 10:00:00
(1 row)


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Sun, Jul 30, 2006 at 10:00:30AM +0200, Andreas Kretschmer wrote:
> Ron Johnson <ron.l.johnson@cox.net> schrieb:
> > >> I know I can do a select to_date(now(),'yyyy-mm-dd') and it will return the
> > >> date.  However, how do I get the time?  Also, is this the proper way to get
> > >> the date portion of a timestamp?
> > >
> > > select now()::timetz;
> > > select now()::time;
> > > select now()::date;
> >
> > What's the inverse?  Say I have a DATE and a TIME, and want to
> > create a TIMESTAMP with them?
>
> You can CAST it:
>
> test=# select '2006/07/29 10:00:00'::timestamp;
>       timestamp
> ---------------------
>  2006-07-29 10:00:00
> (1 row)

Or the easy way:

select '2006/07/29'::date + '10:00:00'::time;

No need to do anything odd at all...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Ron Johnson <ron.l.johnson@cox.net> writes:
> What's the inverse?  Say I have a DATE and a TIME, and want to
> create a TIMESTAMP with them?

Add 'em together, using the "date + time" or "date + timetz" operator:

regression=# select current_date + '11:57'::time;
      ?column?
---------------------
 2006-07-30 11:57:00
(1 row)

regression=# select current_date + '11:57-04'::timetz;
        ?column?
------------------------
 2006-07-30 11:57:00-04
(1 row)

See "Date/Time Operators" in the manual.  I believe these operations
are SQL-standard.

            regards, tom lane