Solved

Extract time from timestamp in teradata while dropping seconds and milliseconds

Posted on 2014-09-10
6
2,973 Views
Last Modified: 2014-09-10
I am trying to convert a timestamp formatted like the following example, 'yyyy-mm-dd hh:mm:ss.fff', to just the time in hours and minutes. I will be using this field later as a grouping dimension so it's important that the seconds and milliseconds are dropped. I would prefer that the result value still be interpreted as true time it is not required so if I were to get a string I would be okay. Lastly and most important the timestamp is in UTC time and I need it to be converted to American eastern.

Any assistance with this would be very much appreciated. Thank you
0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:mcsween
ID: 40316040
This expression in SQL studio will give you HH:MM in -5 GMT (Eastern US) timezone assuming you pass in UTC.  You can replace GETUTCDATE() with your variable.
SELECT CONVERT(VARCHAR(5),DATEADD(hh,-5,GETUTCDATE()),8)

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 40316101
Ah Ha. I was using convert(varchar, timestamp, 108) trying to convert it. In regards to the UTC conversion though. I was under the assumption that UTC time takes into consideration day light savings time so although on this current date we are -5, a few months from now we will be -4. With that said, wouldnt there have to be a way for the function to interpret the date that is being passed?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40316102
I have not worked with Terradata much and have not had to perform these conversions, so the following is just research

Under "TIMESTAMP-to-Character Conversion"

There is an example provided which suggests that you may CAST( ... AS FORMAT '')

so, it might be possible to get to hours and minutes like this:

CAST(your_ts_field AS FORMAT 'HH:MI')

or

CAST( CAST( your_ts_field AS FORMAT 'HH:MI' ) AS CHAR(5) )
0
 
LVL 2

Author Comment

by:bignadad
ID: 40316138
Just tried it out and worked like a charm. Thank you very much for your assistance.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40316141
A pleasure, thanks for the grading. Cheers, Paul

oh, & out of interest were you able to use it without converting to char or varchar?
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question