Splitting Timestamps

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 ===

I believe you would want to cast the field to a date like so select
datefield::datefrom table1 or select datefield::time from table1.

_____

From: Chris Hoover [mailto:revoohc@gmail.com]
Sent: Tuesday, July 25, 2006 11:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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

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.

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

-----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/

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