CODEWITHSUNDEEP
sqloracle-databasedateplsqlstar-schema
Kirluu
Kirluu

Reputation: 43

PLSQL: Query star schema time-dimension without stored date

I have a star schema database with an Hour-Dimension (Time-dimension), with the following columns in it:

ID, ON_HOUR, ON_DAY, IN_MONTH, IN_YEAR

I then query the database, and I want to find all entries within an interval of given dates, based on this Hour-Dimension.

However comparing the ON_DAY attribute with the interval days and so on with IN_MONTH and IN_YEAR, I can often reach a case where I receive no data, if the interval spans over several months. Thus I need to convert these values to a timestamp, however I am querying into the database, so how do I compare my given timestamps with the time data properly? I do not have a stored DATE nor TIMESTAMP in the database - should I change this?

Right now, my best bet is something like this:

to_timestamp('H.IN_YEAR-H.IN_MONTH-H.ON_DAY H.ON_HOUR:00:00', 'YYYY-MM-DD hh24:mi:ss')

This does not seem to work however, and it also looks dodgy, so I didn't really expect it to...

What is the best way to get the entries within my given interval of dates?

Upvotes: 0

Views: 192

Answers (1)

Simon
Simon

Reputation: 36

You appear to be passing a literal string into the timestamp function - you need to pass in the values as a concatenated string using the concat function. Try the below code snippet

(H.IN_YEAR||H-IN_MONTH||H.ON_DAY||H.ON_HOUR,'YYYMMDDHH24')

Upvotes: 1

Related Questions