Home » SQL & PL/SQL » SQL & PL/SQL » Time data type column
Time data type column [message #403465] Fri, 15 May 2009 14:33 Go to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello everybody!

Is there time data type? I have read about timestamp but was wondering if there is anyway I can store the time only e.g. 17:30 or 17:30:00

I have 'event' table in which I need to store the time an event will start. I could use VARCHAR but I might have queries for events that start before or after a certain hour.

Any help is appreciated!
Re: Time data type column [message #403466 is a reply to message #403465] Fri, 15 May 2009 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Just use DATE datatype.
NEVER, EVER store dates or times in VARCHAR2!
Re: Time data type column [message #403470 is a reply to message #403465] Fri, 15 May 2009 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't see any reason to store time without date.
Do you event only in one day? Even in this case why not storing date?

Regards
Michel
Re: Time data type column [message #403473 is a reply to message #403470] Fri, 15 May 2009 15:00 Go to previous messageGo to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Thanks for the replies!

The date is needed so it is going to be stored and it would make sense to store it in the same column as time.

The reason for my question is considering grouping events according to date so for instance under 01-Jan-2009 there will be 3 events with different times. Also
Re: Time data type column [message #403474 is a reply to message #403465] Fri, 15 May 2009 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Time data type column [message #403477 is a reply to message #403465] Fri, 15 May 2009 15:13 Go to previous messageGo to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello again,

The question is still about storing time only in a column. I tried DATE type and inserting 17:00 or 17:00:00 is not allowed, so I guess I will have to stick with TIMESTAMP and store date and time in the same column.

Thanks for the help!
Re: Time data type column [message #403478 is a reply to message #403477] Fri, 15 May 2009 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
LittleB wrote on Fri, 15 May 2009 13:13
Hello again,

The question is still about storing time only in a column. I tried DATE type and inserting 17:00 or 17:00:00 is not allowed, so I guess I will have to stick with TIMESTAMP and store date and time in the same column.

Thanks for the help!


You did it WRONG!


SQL> create table tester (now date);

Table created.

SQL> insert into tester values (to_date('17:00','HH24:mi'));

1 row created.

SQL> select * from tester;

NOW
---------
01-MAY-09



If you would actually show what failed we could tell you what was wrong.
Re: Time data type column [message #403493 is a reply to message #403465] Fri, 15 May 2009 20:22 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
i think you can use extract hour from and concatenate and store it in varchar2 variable
yours
dr.s.raghunathan
Re: Time data type column [message #403494 is a reply to message #403493] Fri, 15 May 2009 20:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
dr.s.raghunathan wrote on Fri, 15 May 2009 18:22
i think you can use extract hour from and concatenate and store it in varchar2 variable
yours
dr.s.raghunathan



You can also poke yourself in the eye, but WHY would you want to do either?
Re: Time data type column [message #403496 is a reply to message #403494] Fri, 15 May 2009 20:41 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear blackswan
since i had a doubt whether i understand OP's request in proper perspective. hope my stand is clear
yours
dr.s.raghunathan
Re: Time data type column [message #403507 is a reply to message #403496] Sat, 16 May 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
hope my stand is clear

/forum/fa/449/0/
I myself understand the text or the goal of less than 5% of your posts, for the rest it is a total mystery for me.

Regards
Michel

[Updated on: Sat, 16 May 2009 01:39]

Report message to a moderator

Re: Time data type column [message #403530 is a reply to message #403507] Sat, 16 May 2009 08:16 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Michel Cadot wrote on Sat, 16 May 2009 12:07
Quote:
hope my stand is clear

/forum/fa/449/0/
I myself

Regards
Michel


Laughing Laughing Laughing
dr.s.raghunathan
Previous Topic: Output of the query
Next Topic: oracle_xml
Goto Forum:
  


Current Time: Sat Feb 08 17:53:25 CST 2025