pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTIME vs. TIMESTAMP data type
4+ messages / 3 participants
[nested] [flat]
* TIME vs. TIMESTAMP data type
@ 2003-02-06 11:04 Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ludwig Lim @ 2003-02-06 11:04 UTC (permalink / raw)
To: pgsql-sql
Hi:
Are there cases when a TIME data type is a better
choice over the TIMESTAMP data type?
It seems that PostgreSQL (I'm using 7.2.3)
encourage its users to use TIMESTAMP over TIME data
type. I said this because of the following:
a) More functions for DATE and TIMESTAMP data types
such as to_date() and to_timestamp(). Howver, function
to_time() does not exist.
b) Same amount of storage for TIMESTAMP and for
TIME. Time with time zone even need more storage space
than a timestamp (12 bytes vs. 8 bytes).
c) It's harder to TIMESTAMP to TIME and vice versa,
while its easier to cast TIMESTAMP to DATE and vice
versa.
thank you very much,
ludwig
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIME vs. TIMESTAMP data type
@ 2003-02-06 11:33 Tomasz Myrta <jasiek@klaster.net>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2003-02-06 11:33 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Ludwig Lim wrote:
> Hi:
>
> Are there cases when a TIME data type is a better
> choice over the TIMESTAMP data type?
>
> It seems that PostgreSQL (I'm using 7.2.3)
> encourage its users to use TIMESTAMP over TIME data
> type. I said this because of the following:
> a) More functions for DATE and TIMESTAMP data types
> such as to_date() and to_timestamp(). Howver, function
> to_time() does not exist.
> b) Same amount of storage for TIMESTAMP and for
> TIME. Time with time zone even need more storage space
> than a timestamp (12 bytes vs. 8 bytes).
> c) It's harder to TIMESTAMP to TIME and vice versa,
> while its easier to cast TIMESTAMP to DATE and vice
> versa.
>
>
> thank you very much,
>
> ludwig
Probably you are right, but you can cast into timestamp before using these functions.
Do you really need to care amount of storage?
Don't forget about INTERVAL type, which is very useful for time calculations.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIME vs. TIMESTAMP data type
@ 2003-02-06 12:08 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 4+ messages in thread
From: Ludwig Lim @ 2003-02-06 12:08 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Hi Tomasz:
--- Tomasz Myrta <jasiek@klaster.net> wrote:
>
> Probably you are right, but you can cast into
> timestamp before using these functions.
> Do you really need to care amount of storage?
I was just thinking if both TIMESTAMP and TIME have
use the same amount of space (I was think TIME might
use less space since it doesn't need to store month,
year, day as compared to TIMESTAMP), and TIMESTAMP
have more functions and is easier to cast, I might as
well use TIMESTAMP.
> Don't forget about INTERVAL type, which is very
> useful for time calculations.
>
--> I'll check that one out.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIME vs. TIMESTAMP data type
@ 2003-02-06 12:17 Julian Scarfe <julian.scarfe@ntlworld.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Julian Scarfe @ 2003-02-06 12:17 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
On 6/2/03 11:04, "Ludwig Lim" <lud_nowhere_man@yahoo.com> wrote:
> Are there cases when a TIME data type is a better
> choice over the TIMESTAMP data type?
Surely this depends on the nature of the data that you want to represent?
If you're researching into sleep patterns and want to represent the times
each day that subjects say they tend to wake and/or fall asleep, you may
want the TIME type, as the important aspect is the time, not the date. The
inclusion of a date would be nonsensical.
If you want to record *when* an event occurred, you usually want date and
time, so TIMESTAMP is more appropriate.
Since event timing is a much more frequent requirement than a time-of-day,
it's not surprising that the facilities may be better developed for dealing
with that type.
Julian Scarfe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-06 12:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-06 11:04 TIME vs. TIMESTAMP data type Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-02-06 11:33 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-06 12:08 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-02-06 12:17 ` Julian Scarfe <julian.scarfe@ntlworld.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox