CODEWITHSUNDEEP
sql
A.H
A.H

Reputation: 1

Is there a quick way to separate date and time from a time stamp in sql?

I am using sql to calculate the average daily temperature and max daily temperature based on a date timestamp in an existing database. Is there a quick way to accomplish this?

I am using dBeaver to do all my data calculations and the following code is what I have used so far:

SELECT
    convert(varchar, OBS_TIME_LOCAL , 100) AS datepart,
    convert(varchar, OBS_TIME_LOCAL, 108) AS timepart,
    CAST (datepart AS date) date_local,
    CAST (timepart AS time) time_local
FROM
    APP_RSERVERLOAD.ONC_TMS_CUR_ONCOR_WEATHER;

The data format as follows:

ID              time_stamp              temp
--------------------------------------------
de2145        2018-07-16 16:55           103

There are multiple IDs with 24hrs of temperature data at 1 min increments.

Upvotes: 0

Views: 663

Answers (2)

VBoka
VBoka

Reputation: 9083

I am not sure if I understand what you need but I will try:

Your question: "Is there a quick way to separate date and time from a time stamp in sql?"

Answer:

select to_char(datec, 'hh24:mi')
       , to_char(datec, 'yyyy-mm-dd') 
from test;

Use to_char with format to select date part and time part.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You seem to want aggregation:

SELECT convert(date, OBS_TIME_LOCAL) AS datepart,
       avg(temp), max(temp)
FROM APP_RSERVERLOAD.ONC_TMS_CUR_ONCOR_WEATHER
GROUP BY convert(date, OBS_TIME_LOCAL);

Upvotes: 0

Related Questions