Unix Epoch Time

[junkie8]

Hi,

I am trying to insert some fields of a table into another table. The time field of the first table stores unix epoch time that I want to be convert into a readable format before being inserted into the second table. I tried the following query but time formatting is not working.

INSERT INTO TimeIconPortionID ( Participant_ID, [Time], IconID, Portion1, Portion2, Portion3, Portion4, Portion5, Portion6 )
SELECT logP1.Participant_ID, Format(DateAdd("s",([logP1.MealTime]-21600),#1/1/1970#),"dd mm yyyy @ hh:mm:ss") AS myTime, logP1.FoodID, logP1.Portion1, logP1.Portion2, logP1.Portion3, logP1.Portion4, logP1.Portion5, logP1.Portion6
FROM logP1;

Any help?

 

[PHV]

What about this ?
Format(DateAdd("s",([logP1.MealTime]-21600),#1/1/1970#),"dd mm yyyy [!]\[/!]@ hh:mm:ss")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[junkie8]

it did not make any difference.

 

[PHV]

but time formatting is not working
So, can you explain your issue ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[junkie8]

the column does not show me a date. I get #Num! error in the time column

 

[PHV]

What is the data type of logP1.MealTime ?
Does it allow Null values ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[junkie8]

Field Size: Double
Format: General Number

 

[junkie8]

it does not allow NULL values

 

[PHV]

And what is the data type of TimeIconPortionID.Time ?
With your query, it should be String.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[junkie8]

even when i run the simple query below

SELECT logP1.PID, DateAdd("s",([logP1.MealTime]-21600),#1/1/1970#) AS myTime, logP1.FoodID, logP1.Portion1, logP1.Portion2, logP1.Portion3, logP1.Portion4, logP1.Portion5, logP1.Portion6
FROM logP1;

without inserting the output in a new table, i am getting the same error.

 

[junkie8]

okay, i got it. I had to divide the MealTime by 1000 because the "s" in Dateadd function stands for seconds but my unix epoch time is in milliseconds.

thank you so much for pushing me in the right direction.