CODEWITHSUNDEEP
sqlpostgresql
momokjaaaaa
momokjaaaaa

Reputation: 1303

convert bigint to date so that I can use in where clause

My date is of type bigint. How can I select date?

Typical query to get date is like this

select date from my_table where date<='20150101'

The problem right now is, date is of type bigint

Upvotes: 2

Views: 8953

Answers (4)

TommCatt
TommCatt

Reputation: 5636

Assuming you know that the best solution is to have a date field defined as a date data type. Also assuming you have no control over the data form, which is common.

It would appear that your bigint value is in the form yyyymmdd. That would make sense as it would give the same sort order as if it was a date value and the date value is human readable without conversion. That being the case, the obvious solution is to not try to convert the date values. That would apply a conversion to every row you examined, possibly thousands and thousands for every query.

Instead, convert the search value. Then you execute only one conversion and apply the result to thousands and thousands of rows of raw data.

declare  bigint intdate = date_part( 'year', mydate ) * 10000
                        + date_part( 'month', mydate ) * 100
                        + date_part( 'day', mydate ); --> Performed once
select   ...
from     mytable
where    ...
    and  intdatefield <= intdate;

I show the conversion and query as separate operations. You could put the conversion directly in the where clause of the query. It will be performed once and the result cached -- so no difference as far as performance. The two-step is just a little easier to understand and maintain.

Now you may need to return a date value in the result set. Here you have some choices. One is to convert the integer value to a date in the query itself:

select  To_Date_Fn( intdatefield / 10000, -- year
                    intdatefield / 100 % 100 -- month
                    intdatefield % 100 )  -- day
        as DateField
...

This is fine when the final result set is small relative to the number of rows searched. You're performing much fewer conversions.

Or you can just let the app do the conversions. From a strictly performance perspective, that will be the best but you could get blowback from the app developers.

Upvotes: 0

Tedo G.
Tedo G.

Reputation: 1565

you should use CAST like this:

WHERE CAST(date AS DATE) <='2015-01-01'

Upvotes: 0

momokjaaaaa
momokjaaaaa

Reputation: 1303

The solution is just simple

select to_date(CAST(date as TEXT), 'YYYYMMDD') from my_table
Where to_date(CAST(date as TEXT), 'YYYYMMDD')<=DATE '20141127' ;

Upvotes: 0

cyberj0g
cyberj0g

Reputation: 3797

Use:

select to_timestamp(date) from my_table where to_timestamp(date)<=DATE '2015-01-01'

Upvotes: 3

Related Questions