3

After performing the following:

INSERT INTO times_table (start_time, end_time) VALUES (to_date('2/3/2016 12:05',
'MM/DD/YYYY HH24:MI'), to_date('2/3/2016 15:05', 'MM/DD/YYYY HH24:MI'));

PostgreSQL only displays the date.

If possible, would I have to run a separate select statement to extract the time (i.e. 12:05 and 15:05), stored in that field? Or are the times completey discard when the query gets executed.

I don't want to use timestamp, since I'd like to execute this in Oracle SQL as well.

gimmegimme
  • 331
  • 7
  • 20

1 Answers1

1

to_date returns... a date. Surprise! So yeah, it's not going to give you the time.

You should be using the timestamp data type to store times and functions which return timestamps. So use to_timestamp.

Oracle also has a timestamp data type and to_timestamp function.

In general, trying to write one set of SQL that works with multiple databases results in either having to write very simple SQL that doesn't take advantage of any of the database's features, or madness.

Instead, use a SQL query builder to write your SQL for you, take care of compatibility issues, and allow you to add clauses to existing statements. For example, Javascript has Knex.js and Perl has SQL::Abstract.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • 1
    Okay thanks. Re-assigning the datatype from date to timestamp and then changing to_date() to the, to_timestamp() function, fixed it. Thank you. – gimmegimme Nov 17 '16 at 16:11