Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Simple TIMESTAMP conversion
Thu, Jun 23 2022 12:42 PMPermanent Link

Mirco Malagoli

Hi,
I'm losing my mind over a simple conversion ...
I have a field (bigint) in milliseconds from 01-01-1970 that I have to display in "yyyy-mm-ss hhnnss"
Thanks!

Mirco
Thu, Jun 23 2022 9:15 PMPermanent Link

Terry Swiers


> I'm losing my mind over a simple conversion ...

Been there myself

> I have a field (bigint) in milliseconds from 01-01-1970 that I have to display in "yyyy-mm-ss hhnnss"

select <millisecondfield>,
 date '1970-01-01' + cast(millisecondfieldas interval MSECOND),
 CAST(date '1970-01-01' + cast(millisecondfieldas interval MSECOND) AS VARCHAR(20) DATE FORMAT 'yyyy-mm-dd' TIME FORMAT 'hhmmss')
from <tablename>

The first expression is yyyy-mm-dd hh:mm:ss, and the second is yyyy-mm-dd hhmmss
Thu, Jun 23 2022 9:18 PMPermanent Link

Raul

Team Elevate Team Elevate

On 6/23/2022 12:42 PM, Mirco Malagoli wrote:
> Hi,
> I'm losing my mind over a simple conversion ...
> I have a field (bigint) in milliseconds from 01-01-1970 that I have to display in "yyyy-mm-ss hhnnss"

I hope somebody else can offer a better solution but something like this
should work  - first one is timestamp and second casts into string value.

I found that i was not able to use MSECOND as interval since the value
appeared to overflow internally so i divided by 1000 and treating it as
seconds instead which for this purpose should be OK as long as your
don't need milliseconds.

Assume field is called MSECSince1970 and table us MyTable


select CAST('1970-01-01' as TIMESTAMP) + CAST(CAST((MSECSince1970/1000)
as int) as INTERVAL SECOND) from MyTable;

or

SELECT CAST(CAST('1970-01-01' as TIMESTAMP) +
CAST(CAST((MSECSince1970/1000) as int) as INTERVAL SECOND) AS VARCHAR(18)
DATE FORMAT 'yyyy-mm-dd' TIME FORMAT 'hhmmss')
FROM MyTable;


Raul
Fri, Jun 24 2022 2:27 AMPermanent Link

Mirco Malagoli

ok this is what i used for a while to get a time result that worked
"SELECT TIME '00:00' + CAST(CAST(V.usRx/1000 AS INT) AS INTERVAL MSECOND) AS transito"

now in this query

SELECT V.*, G.*,
(SELECT CAST('1970-01-01' as TIMESTAMP) + CAST(CAST((usRx/1000000) AS INT) AS INTERVAL SECOND)  FROM giri
 WHERE idBatt = G.idBatt AND idNome = G.idNome AND tipo = :tipoPitIn AND invalid = false AND invalidMan = false AND (transp > 9 OR idNome > 0)
 ORDER BY ABS(usRx-G.usRx)
 RANGE 1 TO 1) AS orarioPitIn,
(SELECT usRx FROM giri
 WHERE idBatt = G.idBatt AND idNome = G.idNome AND tipo = :tipoPitOut AND invalid = false AND invalidMan = false AND (transp > 9 OR idNome > 0)
 ORDER BY ABS(usRx-G.usRx)
 RANGE 1 TO 1) AS orarioPitOut
FROM veloci V
JOIN giri G ON V.idgiro = G.idx
WHERE idBatt = :idbatt
AND V.tipo >= SurprisedfsPit AND G.invalid = false AND G.invalidMan = false AND (G.transp > 9 OR G.idNome > 0)
ORDER BY idnome, giro

the execution time has become 250 seconds versus 4 if there is no time conversion
Is there anything I can do?
Thanks!
Fri, Jun 24 2022 11:57 PMPermanent Link

Terry Swiers


> the execution time has become 250 seconds versus 4 if there is no time conversion Is there anything I can do?

Create a calculated field that contains the text you want to display.  That way the cost of generating the display text is incurred once when the data is saved, rather than every time it is queried.  
Image