concatenating variables for use as timestamp

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

concatenating variables for use as timestamp

Post by nnani » Mon Oct 07, 2013 1:45 pm

Hello All,

I have a scenario, where I am using two variables one for data and one for time.
I want to concatenate it and use the concatenated value for filtering purpose on load_dt_tm column in my select query

The variable value is set like this

Code: Select all

\set

PROC_DT = ''20130501''
PREV_PROC_DT = ''20130430''
PROC_TM = ''235959''
PREV_PROC_TM = ''000000''
These are the values which are allocated to variables. I am showing them using the \set command

Now, I want to compare load_dt_tm(timestamp) column to a PREV_PROC_DT+PREV_PROC_TM value

Code: Select all

select * from test where
load_dt_tm > (:PREV_PROC_DT::VARCHAR(15)::DATE||' '||CAST(:PREV_PROC_TM  as timestamp))
The above concatenation does not work.

And also, if the PREV_PROC_TM = ''000000'' then it takes the value as 0.

I have found the workaround, but its too much tedious to be used

Code: Select all


select * from test where
load_dt_tm >
  (:PREV_PROC_DT::VARCHAR(15)::DATE||' '||
 ((CASE WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 5 THEN '0' || :PREV_PROC_TM
  WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 4 THEN '00' || (:PREV_PROC_TM :: VARCHAR(6))
  WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 3 THEN '000' || (:PREV_PROC_TM :: VARCHAR(6))
  WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 2 THEN '0000' || (:PREV_PROC_TM :: VARCHAR(6))
  WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 1 THEN '00000' || (:PREV_PROC_TM :: VARCHAR(6))
  WHEN LENGTH(:PREV_PROC_TM :: VARCHAR(6)) = 0 THEN '000000' || (:PREV_PROC_TM :: VARCHAR(6))
  ELSE (:PREV_PROC_TM :: VARCHAR(6))
  END ) ::VARCHAR(15)::TIME))::TIMESTAMP
Is there any other way we can achieve this.
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: concatenating variables for use as timestamp

Post by id10t » Mon Oct 07, 2013 2:09 pm

Code: Select all

daniel=> select date(:PROC_DT), date(:PREV_PROC_DT);
    date    |    date    
------------+------------
 2013-05-01 | 2013-04-30
(1 row)

Code: Select all

daniel=> select to_timestamp(:PROC_DT || :PROC_TM, 'YYYYMMDDHHMISS'), to_timestamp(:PREV_PROC_DT || :PREV_PROC_TM, 'YYYYMMDDHHMISS');
    to_timestamp     |    to_timestamp     
---------------------+---------------------
 2013-05-01 23:59:59 | 2013-04-30 00:00:00
(1 row)

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: concatenating variables for use as timestamp

Post by nnani » Mon Oct 07, 2013 2:42 pm

Hi skwa,

Can you please show me how did u set the variables

I am setting it like this

Code: Select all

vsql -v PROC_DT='20130501' -v PREV_PROC_DT='20130430' -v PROC_TM='235959' -v PREV_PROC_TM='000000' -f filename
I tried like this

Code: Select all

\set PROC_DT '20130501'

\set
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'
PROC_DT = '20130501'

nnani=> select date(:PROC_DT);
    date
-------------
 55116-06-10
(1 row)

Not sure where I am going wrong

Thanks
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: concatenating variables for use as timestamp

Post by id10t » Mon Oct 07, 2013 2:56 pm

Code: Select all

daniel@synapse:~$ cat << EOF | vsql -v PROC_DT='20130501' -v PREV_PROC_DT='20130430' -v PROC_TM='235959' -v PREV_PROC_TM='000000'
select date(:PROC_DT::varchar), date(:PREV_PROC_DT::varchar);
select to_timestamp(:PROC_DT || :PROC_TM, 'YYYYMMDDHHMISS'), to_timestamp(:PREV_PROC_DT || :PREV_PROC_TM, 'YYYYMMDDHHMISS');
EOF

    date    |    date    
------------+------------
 2013-05-01 | 2013-04-30
(1 row)

    to_timestamp     |    to_timestamp     
---------------------+---------------------
 2013-05-01 23:59:59 | 2013-04-30 00:00:00
(1 row)

Code: Select all

daniel@synapse:~$ cat nnani 
\set PROC_DT        '''20130501'''
\set PREV_PROC_DT   '''20130430'''
\set PROC_TM        '''235959'''
\set PREV_PROC_TM   '''000000'''


\echo
select date(:PROC_DT), date(:PREV_PROC_DT);
\echo
select to_timestamp(:PROC_DT || :PROC_TM, 'YYYYMMDDHHMISS'), to_timestamp(:PREV_PROC_DT || :PREV_PROC_TM, 'YYYYMMDDHHMISS');

Code: Select all

daniel@synapse:~$ vsql -f nnani 

    date    |    date    
------------+------------
 2013-05-01 | 2013-04-30
(1 row)


    to_timestamp     |    to_timestamp     
---------------------+---------------------
 2013-05-01 23:59:59 | 2013-04-30 00:00:00
(1 row)
Last edited by id10t on Mon Oct 07, 2013 3:05 pm, edited 1 time in total.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: concatenating variables for use as timestamp

Post by nnani » Mon Oct 07, 2013 3:03 pm

That did the trick :D

Thanks for the quick reply skwa
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica SQL”