0 - 38 of 38 tags for timestamp

Hi,
I was suggested by DBA to change in one of tables , the partitioning column from timestamp to date.
In explain plan I can see that in both cases partition prunning takes place.
Is there anybody that is master in partitioning in teradata and can help if there is some logical reasoning for such change ?
 
Regards,

my timestamps are in this format 1405720077410
Wondering if anyone knows how to correctly convert it to date? I have tried below but day is off by one day
cast((cast(cast(700101 as date) +  eventtime_
 / 86400 as timestamp(6)) +
     ( eventtime_
 mod 86400) * interval '00:00:01' hour to second ) as timestamp(6)) date_

Hi,
  Can we compress Timestamp field in TD14 (I know that Timestamp fields for only NULL values can be compressed in earlier versions). If yes, can the syntax also be provided?
 
Thanks for the help

Hi all,
I am writing CLIv2 program (acutally this will be external procedure later) and have following question. When issuing following query "SELECT current_timestamp(0)" in the program to Teradata the result row is represented in the CHAR format YYYY-MM-DD HH:MI:SS. Is it possible to make CLIv2 return TimeStamp data type, I mean:
 

Hi Experts,
 
I have the below query in whcih i need to compare timestamp value 'YYYY-MM-DDBHH:MI:SS.S(6)  with
a date value in format   '01/23/2014 11:53:50.000000' as below , but i am getting error as invalid timestamp
  

Running the following query:

SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime

 Returns the result of:

Dear All,
I have a Table where i have 2 fields - date_col and time_col.
DATE_COL - DATE FORMAT 'YY/MM/DD'
TIME_COL - TIME(6)
I am trying to select the min(time_col) for a particular date / current_date...
When i select all the records from the table for a particular date, i can see the min of time_col is '00:01:33'

Hello.
I run a number of simple programs in SQL Assistant that insert rows into tables for predictive modeling (I'm a user, not a DBA).  Before each insert I make a copy of the target table as a backup.   The backup table's name is the target table's name with a "_bu" suffix.

Hi,
This is my first post in this forum. I am new to teradata DB. There is a project that I am working on where we are receiving the date in varchar format :
YYYY-MM-DD HH:MI:SS NNNNNN 

Hi
I need to load a file using mload where  the load clm has a data like this 03/29/2013 12:00:00 PM.
select cast(cast('03/29/2013 12:00:00 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22))
I need it to load as Timestamp rather than varchar
 
Thanks
Balu
 

Hi,
My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

Hi
My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p

I have a timestamp with time zone field and I would like to get the number of seconds that have elapsed from the start of that day.  This seems like it should be very trivial but I've searched for hours and tried many things that don't work.  What is a simple method for getting the number of seconds?

I have timestamp values in Varchar field. Trying to populate this data in a table where field types are properly defined, and I am unable to push data from table a (timestamp is in varchar) to table b (where I want the date to be in timestamp field). Getting error message 2666. How can I find out fields with bad dates only?

I am bumping into timestamp conversion error when loading to timestamp column with time zone. The table in the column define as 
device_dt_ut TIMESTAMP(0) WITH TIME ZONE
​snippet of fastload:
 

0005 DEFINE

        device_dt_ut (VARCHAR(32)),

 

 

Hi all,

In my blog entries "Teradata Columnar" and "9.2 Quintillion? What's that all about?", I discussed column partitioning and the increased partition limit that are introduced in TD 14.0.  But there are other TD 14.0 partitioning enhancements that you may find useful.  The following provides a brief introduction to these enhancements; for more detailed information, see the Orange Book: Increased Partition Limit and other Partitioning Enhancements, the Orange Book: Teradata Columnar, and the TD 14.0 manuals.

estimated cardinality for the following predicate towards timestamp column is far too high vs date column

1) Table DDL:

--------------------------------------

Tables that are dropped and reloaded daily, how can I determine if the table has been today's load, not yesterday's?

The Teradata Temporal feature, available with Teradata 13.10 allows the customer to capture, track, and analyze the full history of evolving business data rather than just the most current updates.

Hello,

 

I'm very much new to Teradata and writting queries as such. I have a CHAR Type column which contains data as follows

'2012-01-20 02:32:37.000000'.

 

How do I convert this CHAR type data to DATE Type, preferably as a timestamp?

 

Regards,

ASR

Hi,

 

I just needed to get the difference between 2 Timestamps in minutes and couldn't find any useful way on the net, so I decided to add my own solution here, in case someone else needs something similar.

 

Hello,

I am sometimes encountering a syntax error (3706) when using CASE with an IN clause, see (1) below.  The error is happening with timestamp columns and sometimes with varchar's (when the individual varchar values in the IN are concatenated, ie. col1 || col2).  Any info on this?
Thanks!

I am trying to call Stored Procedure with parameters. Procedure have two TIMESTAMP parameters for example use below one:

Teradata Database 14.0's new partition limit of 9223372036854775807 (over 9 quintillion) is coming!

Hi All,

Can you please explain the concept for timestamp(0),timestamp(1) ....timestamp(6)..?

Thanks in advance for the help.

Is it possible to get the timestamp to display the timezone like this using SQL?
2011-04-08 09:16:10 PM Central Standard Time

What I have so far:

SQL:
SELECT
current_timestamp as myDefaultTimestamp
, cast(cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z') as varchar(40)) as myTimeZone
, cast(
cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)') as varchar(40)) as myTimeZone2

RESULTS:
myDefaultTimestamp
2011-04-08 21:16:10.110000+00:00

myTimeZone
2011-04-08 21:16:10.110000

myTimeZone2

This gets the date, how can I get the full timestamp?

SELECT (CAST('1970/01/01' AS DATE FORMAT 'yyyy/mm/dd')) + CAST(my_time/86400 AS INTEGER) AS CONVERTED_TIME
FROM myDatabase.myTable

I have a table with a column that is an integer and has Unix epoch time data.

Hi

Is there a way to convert TIMESTAMP data to ISO 8061 format, so the result will be something like: ' 2011-04-04T12:57:18.530'?

My data:
15-MAR-11 07.54.08.000000 AM
15-MAR-11 07.53.40.000000 AM
15-MAR-11 07.53.04.000000 AM
15-MAR-11 07.52.55.000000 AM
15-MAR-11 07.52.16.000000 AM

My current DDL:
CREATE multiset TABLE dbname.dbtable
(
CREATE_DATE timestamp(6)
);

My current TPT Insert statement:
'INSERT INTO '||@TargetDatabase||'.dbtable
(
CREATE_DATE = :CREATE_DATE (TIMESTAMP, FORMAT ''DD-MMM-YYBHH.MI.SS.S(6)BT'')
);'

The data is being stored in the database table with century of '19' instead of '20'. My data will always be century of '20':

CREATE_DATE
1911-03-15 07:54:08

Hello Folks,
I am new to Teradata.

I have a date column in one table (Table A) that I have to insert into a date column in another table (Table B)

However,
Table A date type: TIMESTAMP (6)
Table B date type: TIMESTAMP (0)

Problem: How do you add or subtract a "fractional second" to a TIMESTAMP column in Teradata?

The available documentation is not very clear, so let's look into it using examples.

Basic use of INTERVAL

Build, Populate, and Query Test Table

Hello,
I have a strange error.
I am creating one table with foreign key on the same table :

CREATE TABLE TEST_B
(TID INTEGER NOT NULL,
PASSRESETDATE TIMESTAMP,
BACTIVE BYTEINT NOT NULL,
TEST_ID INTEGER,
CONSTRAINT PK_TEST_B PRIMARY KEY (TID));

ALTER TABLE TEST_B
ADD CONSTRAINT FK_TEST_B FOREIGN KEY (TEST_ID)
REFERENCES WITH CHECK OPTION TEST_B(TID);

I insert some data with java program :

PreparedStatement pstmt =null;
pstmt= conn.prepareStatement("INSERT INTO TEST_B (TID,BACTIVE,PASSRESETDATE,TEST_ID) VALUES (?,?,?,?)");
for(int i=1;i<3;i++){

Hi,

Would anyone know why the difference of two timestamp(6) columns in interval day(4) to second(6) is coming out all nulls in Hyperion 9.3.1? A calculated field with formula "(col2 - col1) interval day(4) to second(6)" is always NULL even though the two columns have different timestamp values.

Just to give you an idea, below is the equivalent in SQL Assistant that works:

create volatile table mytable ( col1 timestamp(6) , col2 timestamp(6) ) primary index (col1) on commit preserve rows;

delete from mytable;
insert into mytable
values (

From the TD user manual, i got this as an example of timestamp to time conversion:

SELECT CAST(cast('1997-12-31 23:59:59' as timestamp(5)) AS TIME(5) WITH TIME ZONE);

But why the time zone? how can I extract just the time with no timezone. I didn't define any timezones anywhere...

Any thoughts on this would be great

Hello!

I've a problem when inserting null value to TIMESTAMP column. I use arrays of parameters and insert two rows with one statement. I receive error when executing statement: "Invalid timestamp".

Problem occurs only when one row has value and other is null. If two has value or are null, then problem doesn't occur. There is no problems in similar situation with another data types (e.g. VARCHAR, INTEGER).

Here is my code (without error handling etc.):


SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)2, 0);