Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Simple TIMESTAMP conversion |
Thu, Jun 23 2022 12:42 PM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Raul 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 AM | Permanent 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 >= fsPit 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 PM | Permanent 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. |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |