Oracle/PHP syntax to grab and later store a timestamp value in a date field

Question

There is a composite primary key stored in a DB that consists of a date field and a foreign key ID. Normally this would create duplicates however the date field (although it only displays the day, month, year appears to have timestamp information stored as well)

My question is how to extract the timestamp information (I think using the to_char field) and more importantly, how I can later insert a record and store the date and timestamp.

Right now I can store a date but not sure the syntax I would need to use to add the time to the Date field insert so it can be consistent with the values I pull from a table select.

Answer 1

To see the full date and time you can use a TO_CHAR format mask like this:

select to_char (datecol, 'DD-MON-YYYY HH24:MI:SS') from mytable;

When inserting a literal date and time, use TO_DATE:

insert into mytable (datecol)
values (to_date('01-JAN-2010 11:23:45','DD-MON-YYYY HH24:MI:SS');

That format is just one possibility. For example you could instead use any of these:

YYYY-MM-DD HH24:MI:SS
MM/DD/YYYY HH24:MI:SS

Answer 2

The Oracle DATE data type includes the time portion.

To get the date from a DATE column using TO_CHAR:

TO_CHAR(date_column, 'DD-MM-YYYY')

---

...not sure the syntax I would need to use to add the time to the Date field insert so it can be consistent with the values I pull from a table select.

To specify the time portion to an existing date, use a combination of TO_DATE and TO_CHAR:

TO_DATE(TO_CHAR(date_column, 'DD-MM-YYYY') || ' 23:59:00', 'DD-MON-YYYY HH24:MI:SS')

...changing 23:59:00 to whatever time you actually want. The double pipe (||) is what Oracle uses for string concatenation (it's also now ANSI standard).